MySQL CASE THEHEN THEN时为空 [英] MySQL CASE WHEN THEN empty case values

问题描述

    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

这是我的查询.结果如下:

This is my query. These are the results:

但是，如果table.age在一个类别中没有至少1个年龄，它将完全忽略结果中的这种情况.像这样:

However if the table.age doesn't have at least 1 age in a category, it will just flat out ignore that case in the result. Like such:

此数据集没有任何年龄< 18.因此年龄带< 18"没有出现.我怎样才能使它显示并返回值0?

This data set didnt have any records for age < 18. So the ageband "<18" doesnt show up. How can I make it so it does show up and return a value 0??

推荐答案

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

You need a table of agebands to populate the result for entries that have no matching rows. This can be done through an actual table, or dynamically generated with a subquery like this:

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

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

这篇关于MySQL CASE THEHEN THEN时为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！