MySQL CASE THEHEN THEN时为空

MySQL CASE THEHEN THEN时为空

您需要一个年龄表来填充没有匹配行的条目的结果。这可以通过实际表完成，也可以通过子查询动态生成，如下所示：

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) tright 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

演示：http ://www.sqlfiddle.com/#!2/
7e2a9/10