目的:利用Northwind数据库的Orders,Order Details,Products表实现每一种产品按年月统计销售量,按产品名称和年月做标题显示。如下图所示:
1
--按年月统计
2
DECLARE @StartYear int
3
DECLARE @Year nvarchar(4)
4
DECLARE @EndYear int
5
DECLARE @Month int
6
DECLARE @M nvarchar(2)
7
DECLARE @SQl nvarchar(MAX)
8
SET @SQL='SELECT OD.ProductID,ProductName'
9
10
SELECT @StartYear=DATENAME(YEAR,MIN(OrderDate)) FROM [Orders]
11
SELECT @EndYear=DATENAME(YEAR,MAX(OrderDate)) FROM [Orders]
12
13
WHIlE(@StartYear<=@EndYear)
14
BEGIN
15
SET @Year=CAST(@StartYear AS varchar(4))
16
SET @Month=1
17
WHILE(@Month<13)
18
BEGIN
19
SET @M=right('0'+CAST(@Month as NVARCHAR(2)),2)
20
SET @SQL=@SQL+',SUM(CASE WHEN (DATENAME(YEAR,OrderDate)='''+@Year+''' AND DATENAME(Month,OrderDate)='''+@M+''') THEN Quantity ELSE 0 END) as '''+@Year+'-'+@M+''''
21
SET @Month=@Month+1
22
END
23
24
SET @StartYear=@StartYear+1
25
END
26
27
SET @SQL=@SQL+' FROM Orders O LEFT OUTER JOIN [Order Details] OD ON O.OrderID=OD.OrderID'+
28
' LEFT JOIN [Products] P on OD.ProductID=P.ProductID '+
29
' GROUP BY OD.ProductID,ProductName ORDER BY OD.ProductID'
30
exec(@SQL)
31
print(@SQL)

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

其实,这里最主要的代码是组合CASE语句,还可以考虑把第一个年份的前面月份和最后年份的后面月份去掉。随便也贴下代码:
1
2
3
--按年月统计
4
DECLARE @StartYear int
5
DECLARE @Year nvarchar(4)
6
DECLARE @EndYear int
7
DECLARE @TmpYear int
8
DECLARE @Month int,@Month1 int,@Month2 int
9
DECLARE @MaxMonth int
10
DECLARE @M nvarchar(2)
11
DECLARE @SQl nvarchar(MAX)
12
SET @SQL='SELECT OD.ProductID,ProductName'
13
14
SELECT @StartYear=DATENAME(YEAR,MIN(OrderDate)),@Month1=DATENAME(Month,MIN(OrderDate)) FROM [Orders]
15
SELECT @EndYear=DATENAME(YEAR,MAX(OrderDate)),@Month2=DATENAME(Month,MAX(OrderDate)) FROM [Orders]
16
17
SET @TmpYear=@StartYear
18
19
WHIlE(@TmpYear<=@EndYear)
20
BEGIN
21
SET @Year=CAST(@TmpYear AS varchar(4))
22
SET @Month=1
23
SET @MaxMonth=13
24
IF(@TmpYear=@StartYear)
25
SET @Month=@Month1
26
ELSE IF(@TmpYear=@EndYear)
27
SET @MaxMonth=@Month2+1
28
WHILE(@Month<@MaxMonth)
29
BEGIN
30
SET @M=right('0'+CAST(@Month as NVARCHAR(2)),2)
31
SET @SQL=@SQL+',SUM(CASE WHEN (DATENAME(YEAR,OrderDate)='''+@Year+''' AND DATENAME(Month,OrderDate)='''+@M+''') THEN Quantity ELSE 0 END) as '''+@Year+'-'+@M+''''
32
SET @Month=@Month+1
33
END
34
35
SET @TmpYear=@TmpYear+1
36
END
37
38
SET @SQL=@SQL+' FROM Orders O LEFT OUTER JOIN [Order Details] OD ON O.OrderID=OD.OrderID'+
39
' LEFT JOIN [Products] P on OD.ProductID=P.ProductID '+
40
' GROUP BY OD.ProductID,ProductName ORDER BY OD.ProductID'
41
exec(@SQL)
42
print(@SQL)
43
44
45
46

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46
