将Count与Case表达式一起使用
将Count与Case表达式一起使用
提问于 2018-06-09 02:47:01
我可能会不知所措地问，为什么在两个不同的场景中比较Claims_Completed时，这些结果会不同？在这两种情况下，Claims_Completed的值都与Claims_Received相同。
SELECT
DischargeType
,COUNT(CASE WHEN (DateReceived > '2/1/2015' AND DateReceived < DATEADD(dd, 1, '2/28/2015')) THEN 1 ELSE 0 END) AS Claims_Received
,COUNT(CASE WHEN (DateCompleted > '2/1/2015' AND DateCompleted < DATEADD(dd, 1, '2/28/2015')) THEN 1 ELSE 0 END) AS Claims_Completed
FROM Claims GROUP BY DischargeType场景2：
SELECT COUNT(*) AS Claims_Received
FROM Claims
WHERE DateReceived > '2/1/2015' AND DateReceived < DATEADD(dd, 1, '2/28/2015')
GROUP BY DischargeType
SELECT COUNT(*) AS Claims_Completed
FROM Claims
WHERE DateCompleted > '2/1/2015' AND DateCompleted < DATEADD(dd, 1, '2/28/2015')
GROUP BY DischargeType回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-09 02:49:08
您需要sum()而不是count()
SELECT DischargeType,
SUM(CASE WHEN (DateReceived > '2/1/2015' AND
DateReceived < DATEADD(dd, 1, '2/28/2015'))
THEN 1 ELSE 0
END) AS Claims_Received,
SUM(CASE WHEN (DateCompleted > '2/1/2015' AND
DateCompleted < DATEADD(dd, 1, '2/28/2015'))
THEN 1 ELSE 0
END) AS Claims_Completed
FROM Claims
GROUP BY DischargeType;因为count()在这里误解了，因为它会将0算作数值或数据。
Stack Overflow用户
发布于 2018-06-09 02:53:09
COUNT将对序列中所有非空值进行计数。因为1和0都是非空的，所以它会计算所有的值。一种选择是使用NULL而不是0：
SELECT
DischargeType
,COUNT(CASE WHEN (DateReceived > '2/1/2015'
AND DateReceived < DATEADD(dd, 1, '2/28/2015'))
THEN 1 ELSE NULL END) AS Claims_Received
,COUNT(CASE WHEN (DateCompleted > '2/1/2015'
AND DateCompleted < DATEADD(dd, 1, '2/28/2015'))
THEN 1 ELSE NULL END) AS Claims_Completed
FROM Claims
GROUP BY DischargeType页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50766759
复制相关文章
相似问题