SqlServer mssql 按月統計所有部門

標籤：ar   sp   資料   on   2014   bs   as   資料庫   sql   

 以訂單統計為例，前端展示柱狀圖（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.000.000.000.000.000.000.000.000.00741327.00120505.000.00

2）統計出資料庫裡有值的月份，再前端邏輯判斷其他月份補0

SQL語句:

SELECT
UserID,
MONTH ( CreateTime ) as 月份,
SUM( Amount ) as 統計
FROM
Orders
WHERE
YEAR ( CreateTime ) = 2014 -- 這裡假設你要查 2014年的每月的統計。
--其他條件
GROUP BY
UserID, MONTH ( CreateTime )
月份銷售額
10741327.00
11120505.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一月二月三月四月五月六月七月八月九月十月十一月十二月
10.000.000.000.000.000.000.000.000.000.0053495.000.00
20.000.000.000.000.000.000.000.000.00738862.0037968.000.00
30.000.000.000.000.000.000.000.000.002099.0022849.000.00
40.000.000.000.000.000.000.000.000.00366.000.000.00
50.000.000.000.000.000.000.000.000.000.006193.000.00

2）百度看到有人提到列轉行,未看到執行個體，不太清楚具體實現方式。有知道的朋友，請告知，謝謝！
SELECT
UserID,
MONTH ( CreateTime ) as 月份,
SUM( Amount ) as 統計
FROM
Orders
WHERE
YEAR ( CreateTime ) = 2014 -- 這裡假設你要查 2014年的每月的統計。
GROUP BY
UserID,MONTH ( CreateTime )

結果：
UserID月份統計
110738862.00
2102099.00
310366.00
41153495.00
11137968.00
21122849.00
5116193.00

SqlServer mssql 按月統計所有部門