目前我得到空白列但我想要 0 Here 如圖所示。單擊此處查看影像。下面是我的查詢，當回傳的總和為 0 時，我希望所有列都顯示為 0。
SELECT COUNT(*) AS Inserted,
SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS Pending,
SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS Completed,
SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS Failure
FROM [dbo].[ETIME_TIMECARD]
where cast(CreateDateTime as date) = CAST(GETDATE() AS DATE)
Group By cast(CreateDateTime as date)
uj5u.com熱心網友回復：
您沒有得到任何結果，因為您WHERE沒有回傳任何結果，并且您GROUP BY沒有任何可分組的內容。事實上，GROUP BY似乎不需要。
SELECT COUNT(*) AS Inserted,
ISNULL(SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END),0) AS Pending,
ISNULL(SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END),0) AS Completed,
ISNULL(SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END),0) AS Failure
FROM [dbo].[ETIME_TIMECARD]
WHERE CreateDateTime >= CAST(GETDATE() AS date)
AND CreateDateTime < CAST(DATEADD(DAY, 1, GETDATE()) AS date);
不過，就我個人而言，我會切換到 a ，COUNT因為您顯然正在計數，然后您就不需要了ISNULL，因為 aCOUNT不會回傳NULL：
SELECT COUNT(*) AS Inserted,
COUNT(CASE status WHEN 0 THEN 1 END) AS Pending,
COUNT(CASE status WHEN 1 THEN 1 END) AS Completed,
COUNT(CASE status WHEN 2 THEN 1 END) AS Failure
FROM [dbo].[ETIME_TIMECARD]
WHERE CreateDateTime >= CAST(GETDATE() AS date)
AND CreateDateTime < CAST(DATEADD(DAY, 1, GETDATE()) AS date);
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/391744.html
標籤：sql sql-server 查询语句
上一篇：如何從陣列的每一列中減去第一列