表Sale
CustID SaleDate SaleNum
0001 2008-9-2 10
0001 2008-9-3 20
0001 2008-10-1 5
0002 2008-10-5 15
0002 2008-11-6 20
0004 2008-9-14 6表Customer
CustID OpenDate
0001 2008-9-1
0002 2008-10-3
0003 2008-12-4
0004 2008-9-13获取当月新开门店的销售数据情况。
结果为:
2008-9 36
2008-10 15
该SQL语句该如何写?谢谢
CustID SaleDate SaleNum
0001 2008-9-2 10
0001 2008-9-3 20
0001 2008-10-1 5
0002 2008-10-5 15
0002 2008-11-6 20
0004 2008-9-14 6表Customer
CustID OpenDate
0001 2008-9-1
0002 2008-10-3
0003 2008-12-4
0004 2008-9-13获取当月新开门店的销售数据情况。
结果为:
2008-9 36
2008-10 15
该SQL语句该如何写?谢谢
select convert(varchar(7),OpenDate ,120) as 月份,
sum(SaleNum ) as 统计数量
from Customer left join Sale on Customer.CustID = Sale.CustID
group by convert(varchar(7),OpenDate ,120)
差不多就这样吧!
CREATE TABLE #A (ID VARCHAR(10),S_DATE VARCHAR(10),QTY INT)INSERT INTO #A SELECT '0001','2008-09-02',10
INSERT INTO #A SELECT '0001','2008-09-03',20
INSERT INTO #A SELECT '0001','2008-10-01',5
INSERT INTO #A SELECT '0002','2008-10-05',15
INSERT INTO #A SELECT '0002','2008-11-06',20
INSERT INTO #A SELECT '0004','2008-09-14',6
CREATE TABLE #B (ID VARCHAR(10),OPEN_DATE VARCHAR(10))
INSERT INTO #B SELECT '0001','2008-09-01'
INSERT INTO #B SELECT '0002','2008-10-03'
INSERT INTO #B SELECT '0003','2008-12-04'
INSERT INTO #B SELECT '0004','2008-09-13'SELECT A.OPEN_DATE,SUM(B.QTY)QTY/**2008-09 36
2008-10 15FROM
(SELECT DISTINCT SUBSTRING(OPEN_DATE,1,7)OPEN_DATE FROM #B)A
LEFT JOIN #A B ON A.OPEN_DATE=SUBSTRING(B.S_DATE,1,7)
WHERE B.S_DATE>(SELECT MIN(OPEN_DATE) FROM #B
WHERE SUBSTRING(OPEN_DATE,1,7)=A.OPEN_DATE
GROUP BY SUBSTRING(OPEN_DATE,1,7))
GROUP BY A.OPEN_DATE
DROP TABLE #A,#B
INSERT INTO #A SELECT '0001','2008-09-03',20
INSERT INTO #A SELECT '0001','2008-10-01',5
INSERT INTO #A SELECT '0002','2008-10-05',15
INSERT INTO #A SELECT '0002','2008-11-06',20
INSERT INTO #A SELECT '0004','2008-09-14',6
CREATE TABLE #B (ID VARCHAR(10),OPEN_DATE VARCHAR(10))
INSERT INTO #B SELECT '0001','2008-09-01'
INSERT INTO #B SELECT '0002','2008-10-03'
INSERT INTO #B SELECT '0003','2008-12-04'
INSERT INTO #B SELECT '0004','2008-09-13'SELECT A.OPEN_DATE,SUM(B.QTY)QTY
FROM
(SELECT DISTINCT SUBSTRING(OPEN_DATE,1,7)OPEN_DATE FROM #B)A
LEFT JOIN #A B ON A.OPEN_DATE=SUBSTRING(B.S_DATE,1,7)
WHERE B.S_DATE>(SELECT MIN(OPEN_DATE) FROM #B
WHERE SUBSTRING(OPEN_DATE,1,7)=A.OPEN_DATE
GROUP BY SUBSTRING(OPEN_DATE,1,7))
GROUP BY A.OPEN_DATE
DROP TABLE #A,#B
/**
2008-09 36
2008-10 15
INSERT @Sale SELECT 0001,'2008-9-2',10
UNION ALL SELECT 0001,'2008-9-3',20
UNION ALL SELECT 0001,'2008-10-1',5
UNION ALL SELECT 0002,'2008-10-5',15
UNION ALL SELECT 0002,'2008-11-6',20
UNION ALL SELECT 0004,'2008-9-14',6
DECLARE @Customer TABLE(CustID int,OpenDate smalldatetime)
INSERT @Customer SELECT 0001,'2008-9-1'
UNION ALL SELECT 0002,'2008-10-3'
UNION ALL SELECT 0003,'2008-12-4'
UNION ALL SELECT 0004,'2008-9-13'
SELECT CONVERT(VARCHAR(4),DATEPART(yy,OpenDate))+'-'+CONVERT(VARCHAR(2),DATEPART(mm,OpenDate)),SUM(SaleNum)
FROM @Sale s INNER JOIN @Customer c ON s.CustID=c.CustID AND CONVERT(VARCHAR(4),DATEPART(yy,SaleDate))+'-'+CONVERT(VARCHAR(2),DATEPART(mm,SaleDate))=CONVERT(VARCHAR(4),DATEPART(yy,OpenDate))+'-'+CONVERT(VARCHAR(2),DATEPART(mm,OpenDate))
GROUP BY CONVERT(VARCHAR(4),DATEPART(yy,OpenDate))+'-'+CONVERT(VARCHAR(2),DATEPART(mm,OpenDate))2008-10 15
2008-9 36
INSERT INTO #A SELECT '0001','2008-09-03',20
INSERT INTO #A SELECT '0001','2008-10-01',5
INSERT INTO #A SELECT '0002','2008-10-05',15
INSERT INTO #A SELECT '0002','2008-11-06',20
INSERT INTO #A SELECT '0004','2008-09-14',6
CREATE TABLE #B (ID VARCHAR(10),OPEN_DATE VARCHAR(10))
INSERT INTO #B SELECT '0001','2008-09-01'
INSERT INTO #B SELECT '0002','2008-10-03'
INSERT INTO #B SELECT '0003','2008-12-04'
INSERT INTO #B SELECT '0004','2008-09-13'
select A.S_DATE,sum(A.QTY) '数量' from
(select cast(substring(S_DATE,0,8) as varchar) S_DATE,QTY from #A) A where A.S_DATE in
(select cast(substring(OPEN_DATE,0,8) as varchar)OPEN_DATE from #B)
group by A.S_DATE
FROM Sale s inner join Customer c ON s.CustID=c.CustID and convert(varchar(7),OpenDate ,120)=convert(varchar(7),SaleDate ,120)
GROUP BY convert(varchar(7),OpenDate ,120)