mysql – 在count和group by中使用CASE的SQL

我正在使用CASE对表中的数据进行分类并对其进行计数,但结果并不准确

现场演示[here]

select DATE(date) as day,count(*),count(distinct case when name = 'fruit' then 1 else 0 end) as fruits,count(distinct case when name = 'vege' then 1 else 0 end) as vege,count(distinct case when name = 'sweets' then 1 else 0 end) as sweets
from food
group by day
with rollup

我不确定问题是CASE还是字符串匹配=因为没有’甜点’仍然是1？
任何指示我都会感激不尽 最佳答案 您的问题是COUNT计算每个非NULL的结果.在您的情况下,您正在使用：

COUNT(distinct case when name = 'sweets' then 1 else 0 end)

因此,当名称不是糖果时,它会计为0.此外,由于您使用的是DISTINCT,因此它只计算一个或两个值.您应该使用SUM或删除DISTINCT和ELSE 0：

SELECT  DATE(date) as day,COUNT(*),SUM(CASE WHEN name = 'fruit' THEN 1 ELSE 0 END) as fruits,SUM(CASE WHEN name = 'vege' THEN 1 ELSE 0 END) as vege,SUM(CASE WHEN name = 'sweets' THEN 1 ELSE 0 END) as sweets
FROM food
GROUP BY DAY
WITH ROLLUP

要么：

SELECT  DATE(date) as day,COUNT(CASE WHEN name = 'fruit' THEN 1 ELSE NULL END) as fruits,COUNT(CASE WHEN name = 'vege' THEN 1 ELSE NULL END) as vege,COUNT(CASE WHEN name = 'sweets' THEN 1 ELSE NULL END) as sweets
FROM food
GROUP BY DAY
WITH ROLLUP

Here is一个修改过的sqlfiddle.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!