mysql - SQL 在计数和分组依据中使用 CASE

Question

我正在使用 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？任何指示我将不胜感激

Answer 1

您的问题是 COUNT 统计了所有非 NULL 的结果。在您的情况下，您正在使用:

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

因此，当名称不是sweets 时，它会计算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(*),
        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。