在应用当中时常需要按照月份进行统计，比如整年的产量，一月的常量，二月的产量。。。十二月的常量。在表的设计的时候产量表一般有两个列，一个是时间，一个是产量。每月的产量对应表中的多条记录。一般产生的报表有如下格式：
| id |
Total |
Jan |
Feb |
Mar |
Apr |
May |
June |
July |
Aug |
Sep |
Oct |
Nov |
Dec |
| id |
12x |
x |
x |
x |
x |
x |
x |
x |
x |
x |
x |
x |
x |
下面对Nothwind数据库中的雇员2007年的销售情况按月进行统计。首先对这些数据进行感性认识下：
SELECT empid, orderdate, unitprice, qty |
FROM Sales.Orders o JOIN Sales.OrderDetails od |
ON o.orderid = od.orderid |
WHERE DATEPART(YEAR, o.orderdate) = 2007 |
ORDER BY empid, orderdate |
可以得到如下数据，我只拿empid = 1的数据：
| 1 |
2007-01-01 00:00:00.000 |
99.00 |
21 |
| 1 |
2007-01-01 00:00:00.000 |
14.40 |
35 |
| 1 |
2007-01-01 00:00:00.000 |
16.00 |
30 |
| |
…………… |
|
|
| 1 |
2007-02-21 00:00:00.000 |
10.20 |
15 |
| 1 |
2007-02-21 00:00:00.000 |
12.00 |
25 |
| |
…………… |
|
|
| 1 |
2007-03-05 00:00:00.000 |
3.6 |
25 |
| |
…………… |
|
|
| |
…………… |
|
|
| |
…………… |
|
|
| 1 |
2007-12-11 00:00:00.000 |
39.00 |
33 |
| |
…………… |
|
|
empid 为1 的每月在表中都有多条记录。下面采用两种方式去产生需要的报表。
1）使用SUM聚合函数，因为sum参数可以是表达式，那么我使用case逻辑对sum聚合进行控制，比如对一月统计可以采用如下方式：
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Jan |
采用如上方式，我只要在select list里面重复上面的聚合函数，就可以得到想要的报表。 |
SUM(od.unitprice * od.qty) AS Year, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Jan, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Feb, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Mar, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Apr, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS May, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS June, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS July, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Aug, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Seo, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Oct, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Nov, |
SUM(CASE DATEPART(MONTH, o.orderdate) |
END * od.unitprice*od.qty) AS Dec |
FROM Sales.Orders o JOIN Sales.OrderDetails od |
ON o.orderid = od.orderid |
WHERE DATEPART(YEAR, orderdate) = 2007 |
以上的方式逻辑比较清晰，但是list比较长，下面这种方式原理和上面一样，但是做法更巧妙。
SUM(od.qty * od.unitprice) Year, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 1)))) AS Jan, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 2)))) AS Feb, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 3)))) AS Mar, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 4)))) AS Apr, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 5)))) AS May, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 6)))) AS June, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 7)))) AS July, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 8)))) AS Aug, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 9)))) AS Sep, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 10)))) AS Oct, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 11)))) AS Nov, |
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 12)))) AS Dec |
FROM Sales.Orders o JOIN Sales.OrderDetails od |
ON o.orderid = od.orderid |
WHERE DATEPART(YEAR, orderdate) = 2007 |
注意上面sum函数的表达是，比如对于一月，有：
od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 1))) |
如果当前的记录是一月，那么DATEPART(MONTH, o.orderdate)是1， SIGN(DATEPART(MONTH, o.orderdate) – 1) = SIGN(1-1) =SIGN(0) = 0,
od.qty*od.unitprice*(1-ABS(SIGN(DATEPART(MONTH, o.orderdate)-1))) = od.qty * od.unitprice*(1-ABS(0)) = od.qty * od.unitprice * 1
如果当前的记录不是一月，那么DATEPART(MONTH, o.orderdate) >1， SIGN(DATEPART(MONTH, o.orderdate) – 1) = SIGN(>1) = 1,
od.qty*od.unitprice*(1-ABS(SIGN(DATEPART(MONTH, o.orderdate)-1))) = od.qty * od.unitprice*(1-ABS(1)) = od.qty * od.unitprice * 0 = 0
所以od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 1)))计算一月的，
SUM(od.qty *od.unitprice * (1-ABS(SIGN(DATEPART(MONTH, o.orderdate) - 2))))计算二月的。。。
2）使用Pivot进行旋转。
首先得到每个id每个月的销售量，如下：
SELECT empid, DATEPART(MONTH, o.orderdate) Month, unitprice * qty Total |
FROM Sales.Orders o JOIN Sales.OrderDetails od |
ON o.orderid = od.orderid |
WHERE DATEPART(YEAR, o.orderdate) = 2007 |
接下来对按Month列进行旋转，对Total列进行sum聚合计算：
ISNULL(p.[1], 0) + ISNULL(p.[2],0) + ISNULL(p.[3], 0)+ISNULL(p.[4],0)+ |
ISNULL(p.[5], 0)+ISNULL(p.[6],0) + ISNULL(p.[7],0)+ISNULL(p.[8],0)+ISNULL(p.[9],0)+ |
ISNULL(p.[10],0)+ISNULL(p.[11],0)+ISNULL(p.[12],0) As Total, |
P.[1] As Jan, P.[2] Feb, P.[3] AS Mar, P.[4] AS Apr,P.[5] May, P.[6] June, P.[7] July, |
P.[8] Aug, P.[9] Sep, P.[10] Oct, P.[11] Nov, P.[12] Dec |
SELECT empid, DATEPART(MONTH, o.orderdate) Month, unitprice * qty Total |
FROM Sales.Orders o JOIN Sales.OrderDetails od |
ON o.orderid = od.orderid |
WHERE DATEPART(YEAR, o.orderdate) = 2007 |
FOR Month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) |
注意如果表中存在某个employee某月没有销售记录，那么对应的结果null（不可以的），那么就需要ISNULL把null转成0，否则Total为null。
那也可以先按empid,月份进行分组，然后在进行旋转，如下：
ISNULL(p.[1], 0) + ISNULL(p.[2],0) + ISNULL(p.[3], 0)+ISNULL(p.[4],0)+ |
ISNULL(p.[5], 0)+ISNULL(p.[6],0) + ISNULL(p.[7],0)+ISNULL(p.[8],0)+ISNULL(p.[9],0)+ |
ISNULL(p.[10],0)+ISNULL(p.[11],0)+ISNULL(p.[12],0) As Total, |
P.[1] As Jan, P.[2] Feb, P.[3] AS Mar, P.[4] AS Apr,P.[5] May, P.[6] June, P.[7] July, |
P.[8] Aug, P.[9] Sep, P.[10] Oct, P.[11] Nov, P.[12] Dec |
SELECT empid, DATEPART(MONTH, o.orderdate) month, SUM(od.unitprice * od.qty) total |
FROM Sales.Orders o JOIN Sales.OrderDetails od |
ON o.orderid = od.orderid |
WHERE DATEPART(YEAR, o.orderdate) = 2007 |
GROUP BY empid,DATEPART(MONTH, o.orderdate) |
FOR month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) |
以上一共采用四种不同的方式，两种不同的原理对数据进行统计。
浙公网安备 33010602011771号