SELECT CASE WHEN age IS NULL THEN 'Unspecified'
WHEN age < 18 THEN '<18'
WHEN age >= 18 AND age <= 24 THEN '18-24'
WHEN age >= 25 AND age <= 30 THEN '25-30'
WHEN age >= 31 AND age <= 40 THEN '31-40'
WHEN age > 40 THEN '>40'
END AS ageband,COUNT(*)
FROM (SELECT age
FROM table) t
GROUP BY ageband
这是我的查询.这些是结果：
但是,如果table.age在一个类别中没有至少1个年龄,那么它将在结果中忽略该情况.像这样：
该数据集没有任何年龄的记录<因此,年龄段“< 18”没有出现.我怎样才能使它显示并返回值0 ??
最佳答案
您需要一个agebands表来填充没有匹配行的条目的结果.这可以通过实际表来完成,或者使用如下子查询动态生成：
SELECT a.ageband,IFNULL(t.agecount,0)
FROM (
-- ORIGINAL QUERY
SELECT
CASE
WHEN age IS NULL THEN 'Unspecified'
WHEN age < 18 THEN '<18'
WHEN age >= 18 AND age <= 24 THEN '18-24'
WHEN age >= 25 AND age <= 30 THEN '25-30'
WHEN age >= 31 AND age <= 40 THEN '31-40'
WHEN age > 40 THEN '>40'
END AS ageband,COUNT(*) as agecount
FROM (SELECT age FROM Table1) t
GROUP BY ageband
) t
right join (
-- TABLE OF POSSIBLE AGEBANDS
SELECT 'Unspecified' as ageband union
SELECT '<18' union
SELECT '18-24' union
SELECT '25-30' union
SELECT '31-40' union
SELECT '>40'
) a on t.ageband = a.ageband