統計報表 -- sql統計語句

需要資料統計頁面，肯定需要匯出資料，於是，邊學邊寫，完成了一段sql~

統計報表 -- sql統計語句

最早的版本是這樣的：分三條sql查出三種不同的狀態的記錄數(總記錄，未支付，已支付)

SELECT COUNT(*) AS RecordCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' 
SELECT COUNT(*) AS UnPayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 0
SELECT COUNT(*) AS PayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 1 

但是，還有一個需求，對記錄數進行分日期進行統計，於是，需要改進，想到了用 GROUP BY 去實現; 但問題是CreateTime是DateTime, 接著百度之，找到了 CONVERT函式，於是變成了：

SELECT COUNT(*) AS RecordCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' GROUP BY CONVERT(CreateTime,DATE)
SELECT COUNT(*) AS UnPayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 0 GROUP BY CONVERT(CreateTime,DATE)
SELECT COUNT(*) AS PayedCount FROM t_record WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00' AND IsPayed = 1 GROUP BY CONVERT(CreateTime,DATE)

但是，為什麼不能一條sql查出來呢？這樣在便利性還是效能上應該都會有所提升,能否在COUNT帶條件或者在SUM做判斷？於是，最終的sql出現了:

SELECT 
    CONVERT(CreateTime,DATE) AS RecordDate,
    COUNT(*) AS RecordCount, 
    SUM(CASE WHEN IsPayed = 0 THEN 1 ELSE 0 END) AS UnPayedCount,
    SUM(CASE WHEN IsPayed = 0 THEN 1 ELSE 0 END) AS PayedCount,
FROM t_record 
    WHERE CreateTime >= '2019-01-01 00:00:00'  AND CreateTime < '2019-02-01 00:00:00'
GROUP BY CONVERT(CreateTime,DATE)

原文：簡書ThinkinLiu 部落格: IT老五

相關報表圖形繪製：統計報表 -- HignCharts：線形圖餅圖繪製及時間等定製