SqlServer mssql 按月统计所有部门

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

以订单统计为例，前端展示柱状图（Jquery统计）：

表及主要字段描述如下；表名：Orders
1.日期CreateTime
2.金额Amount
3.用户UserID

情况一：
根据部门统计某一年每月销量（查询一个部门月统计）

1）直接在SQL语句中判断每月信息，好处，前台直接调用；坏处，性能不高。

SQL语句:

SELECT
SUM(CASE WHEN MONTH(s.CreateTime) = 1 THEN s.Amount ELSE 0 END) AS '一月',
SUM(CASE WHEN MONTH(s.CreateTime) = 2 THEN s.Amount ELSE 0 END) AS '二月',
SUM(CASE WHEN MONTH(s.CreateTime) = 3 THEN s.Amount ELSE 0 END) AS '三月',
SUM(CASE WHEN MONTH(s.CreateTime) = 4 THEN s.Amount ELSE 0 END) AS '四月',
SUM(CASE WHEN MONTH(s.CreateTime) = 5 THEN s.Amount ELSE 0 END) AS '五月',
SUM(CASE WHEN MONTH(s.CreateTime) = 6 THEN s.Amount ELSE 0 END) AS '六月',
SUM(CASE WHEN MONTH(s.CreateTime) = 7 THEN s.Amount ELSE 0 END) AS '七月',
SUM(CASE WHEN MONTH(s.CreateTime) = 8 THEN s.Amount ELSE 0 END) AS '八月',
SUM(CASE WHEN MONTH(s.CreateTime) = 9 THEN s.Amount ELSE 0 END) AS '九月',
SUM(CASE WHEN MONTH(s.CreateTime) = 10 THEN s.Amount ELSE 0 END) AS '十月',
SUM(CASE WHEN MONTH(s.CreateTime) = 11 THEN s.Amount ELSE 0 END) AS '十一月',
SUM(CASE WHEN MONTH(s.CreateTime) = 12 THEN s.Amount ELSE 0 END) AS '十二月'
FROM Orders AS s
WHERE YEAR(s.CreateTime) = 2014

--其他条件

结果:

一月二月三月四月五月六月七月八月九月十月十一月十二月
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 741327.00 120505.00 0.00

2）统计出数据库里有值的月份，再前端逻辑判断其他月份补0

SQL语句:

SELECT
UserID,
MONTH ( CreateTime ) as 月份,
SUM( Amount ) as 统计
FROM
Orders
WHERE
YEAR ( CreateTime ) = 2014 -- 这里假设你要查 2014年的每月的统计。
--其他条件
GROUP BY
UserID, MONTH ( CreateTime )结果：

月份销售额
10 741327.00
11 120505.00

情况二：
统计所有部门某一年每月销量

1）此数据量大的话影响性能，SQL语句(这里未联查部门表):

SELECT
UserID,
SUM(CASE WHEN MONTH(s.CreateTime) = 1 THEN s.Amount ELSE 0 END) AS '一月',
SUM(CASE WHEN MONTH(s.CreateTime) = 2 THEN s.Amount ELSE 0 END) AS '二月',
SUM(CASE WHEN MONTH(s.CreateTime) = 3 THEN s.Amount ELSE 0 END) AS '三月',
SUM(CASE WHEN MONTH(s.CreateTime) = 4 THEN s.Amount ELSE 0 END) AS '四月',
SUM(CASE WHEN MONTH(s.CreateTime) = 5 THEN s.Amount ELSE 0 END) AS '五月',
SUM(CASE WHEN MONTH(s.CreateTime) = 6 THEN s.Amount ELSE 0 END) AS '六月',
SUM(CASE WHEN MONTH(s.CreateTime) = 7 THEN s.Amount ELSE 0 END) AS '七月',
SUM(CASE WHEN MONTH(s.CreateTime) = 8 THEN s.Amount ELSE 0 END) AS '八月',
SUM(CASE WHEN MONTH(s.CreateTime) = 9 THEN s.Amount ELSE 0 END) AS '九月',
SUM(CASE WHEN MONTH(s.CreateTime) = 10 THEN s.Amount ELSE 0 END) AS '十月',
SUM(CASE WHEN MONTH(s.CreateTime) = 11 THEN s.Amount ELSE 0 END) AS '十一月',
SUM(CASE WHEN MONTH(s.CreateTime) = 12 THEN s.Amount ELSE 0 END) AS '十二月'
FROM Orders AS s
WHERE YEAR(s.CreateTime) = 2014
group by UserID

结果：

UserID 一月二月三月四月五月六月七月八月九月十月十一月十二月
1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 53495.00 0.00
2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 738862.00 37968.00 0.00
3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 2099.00 22849.00 0.00
4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 366.00 0.00 0.00
5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 6193.00 0.00

2）百度看到有人提到列转行,未看到实例，不太清楚具体实现方式。有知道的朋友，请告知，谢谢！

SELECT
UserID,
MONTH ( CreateTime ) as 月份,
SUM( Amount ) as 统计
FROM
Orders
WHERE
YEAR ( CreateTime ) = 2014 -- 这里假设你要查 2014年的每月的统计。
GROUP BY
UserID,MONTH ( CreateTime )结果：
UserID 月份统计
1 10 738862.00
2 10 2099.00
3 10 366.00
4 11 53495.00
1 11 37968.00
2 11 22849.00

5 11 6193.00