MySQL GROUP BY年龄范围,包括空范围

我正在尝试按年龄段计算人数,几乎可以解决两个问题:

  1. 如果没有给定年龄范围(NULL)中的人,则该年龄范围不会出现在结果中。例如,在我的数据中没有“超过80”的条目,因此不会出现日期范围。基本上,当缺少日期范围时,在编程中看起来像是一个错误。

  2. 我想以特定方式订购结果。在下面的查询中,由于ORDER BY按age_range,因此‘20-29’的结果位于’Under 20’的结果之前。

这是数据库表“查询”的示例:

inquiry_id  birth_date

1 1960-02-01

2 1962-03-04

3 1970-03-08

4 1980-03-02

5 1990-02-08

这是查询:

SELECT

CASE

WHEN age < 20 THEN 'Under 20'

WHEN age BETWEEN 20 and 29 THEN '20 - 29'

WHEN age BETWEEN 30 and 39 THEN '30 - 39'

WHEN age BETWEEN 40 and 49 THEN '40 - 49'

WHEN age BETWEEN 50 and 59 THEN '50 - 59'

WHEN age BETWEEN 60 and 69 THEN '60 - 69'

WHEN age BETWEEN 70 and 79 THEN '70 - 79'

WHEN age >= 80 THEN 'Over 80'

WHEN age IS NULL THEN 'Not Filled In (NULL)'

END as age_range,

COUNT(*) AS count

FROM (SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM inquiries) as derived

GROUP BY age_range

ORDER BY age_range

这是基于Wrikken的建议的简单解决方案:

SELECT

SUM(IF(age < 20,1,0)) as 'Under 20',

SUM(IF(age BETWEEN 20 and 29,1,0)) as '20 - 29',

SUM(IF(age BETWEEN 30 and 39,1,0)) as '30 - 39',

SUM(IF(age BETWEEN 40 and 49,1,0)) as '40 - 49',

SUM(IF(age BETWEEN 50 and 59,1,0)) as '50 - 59',

SUM(IF(age BETWEEN 60 and 69,1,0)) as '60 - 69',

SUM(IF(age BETWEEN 70 and 79,1,0)) as '70 - 79',

SUM(IF(age >=80, 1, 0)) as 'Over 80',

SUM(IF(age IS NULL, 1, 0)) as 'Not Filled In (NULL)'

FROM (SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM inquiries) as derived

回答:

除了我喜欢的范围表以外,单行答案可能是:

SELECT

SUM(IF(age < 20,1,0)) as 'Under 20',

SUM(IF(age BETWEEN 20 and 29,1,0)) as '20 - 29',

SUM(IF(age BETWEEN 30 and 39,1,0)) as '30 - 39',

SUM(IF(age BETWEEN 40 and 49,1,0)) as '40 - 49',

...etc.

FROM inquiries;

以上是 MySQL GROUP BY年龄范围,包括空范围 的全部内容, 来源链接: utcz.com/qa/429230.html

回到顶部