问题描述
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时为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!