我正在使用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的结果.在您的情况下,您正在使用：
原文链接：https://www.f2er.com/mysql/432927.htmlCOUNT(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