mysql case when 空_MySQL CASE那么空案例值

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个年龄,那么它将在结果中忽略该情况.像这样：

该数据集没有任何年龄的记录

最佳答案

您需要一个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