以訂單統計為例，前端展示柱狀圖（Jquery統計）：

表及主要字段描述如下；表名：Orders1.日期CreateTime2.金額Amount3.用戶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 sWHERE 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語句:

SELECTUserID,MONTH ( CreateTime ) as 月份,SUM( Amount ) as 統計FROMOrdersWHEREYEAR ( CreateTime ) = 2014 -- 這里假設你要查 2014年的每月的統計。--其他條件GROUP BYUserID, MONTH ( CreateTime )結果：月份    銷售額10    741327.0011    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 sWHERE 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.002    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    738862.00    37968.00    0.003    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    2099.00    22849.00    0.004    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00    366.00    0.00    0.005    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）百度看到有人提到列轉行,未看到實例，不太清楚具體實現方式。有知道的朋友，請告知，謝謝！

SELECTUserID,MONTH ( CreateTime ) as 月份,SUM( Amount ) as 統計FROMOrdersWHEREYEAR ( CreateTime ) = 2014 -- 這里假設你要查 2014年的每月的統計。GROUP BYUserID,MONTH ( CreateTime )結果：UserID    月份    統計1    10    738862.002    10    2099.003    10    366.004    11    53495.001    11    37968.002    11    22849.00

5 11 6193.00