在应用当中时常需要按照月份进行统计,比如整年的产量,一月的常量,二月的产量。。。十二月的常量。在表的设计的时候产量表一般有两个列,一个是时间,一个是产量。每月的产量对应表中的多条记录。一般产生的报表有如下格式:
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)
WHEN 1 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Jan
采用如上方式,我只要在select list里面重复上面的聚合函数,就可以得到想要的报表。
SELECT empid AS Empid,
SUM(od.unitprice * od.qty) AS Year,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 1 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Jan,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 2 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Feb,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 3 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Mar,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 4 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Apr,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 5 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS May,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 6 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS June,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 7 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS July,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 8 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Aug,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 9 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Seo,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 10 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Oct,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 11 THEN 1
ELSE 0
END * od.unitprice*od.qty) AS Nov,
SUM(CASE DATEPART(MONTH, o.orderdate)
WHEN 12 THEN 1
ELSE 0
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
GROUP BY empid
ORDER by empid
以上的方式逻辑比较清晰,但是list比较长,下面这种方式原理和上面一样,但是做法更巧妙。
SELECT empid Empid,
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
GROUP BY empid
ORDER by empid
注意上面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聚合计算:
SELECT empid 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
FROM
(
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
) D1 PIVOT(
SUM(Total)
FOR Month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS P
ORDER BY empid
注意如果表中存在某个employee某月没有销售记录,那么对应的结果null(不可以的),那么就需要ISNULL把null转成0,否则Total为null。
那也可以先按empid,月份进行分组,然后在进行旋转,如下:
SELECT empid 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
FROM (
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)
) D1 PIVOT(
MAX(total)
FOR month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS P
ORDER BY empid
以上一共采用四种不同的方式,两种不同的原理对数据进行统计。