不同年龄段的 MySQL 行数

Question

我有一个 MySQL 表如下。

emp_no  emp_name    dob     gender
1       A        1978-10-10 Male
2       B        1985-02-20 Female
3       C        1982-04-16 Female
4       D        1980-06-27 Male

然后我需要得到下面的结果。

age_group   No of Male  No of Female    Total
<25            1           0             1
25-35          0           1             1
35-50          0           1             1
>50            1           0             1

我使用以下查询来获取此输出。

SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS age_group, 
COUNT( IF( TIMESTAMPDIFF(YEAR, dob, NOW()) <25, 1, 0 ) ) AS ag_C, 
COUNT( IF( TIMESTAMPDIFF(YEAR, dob, NOW()) BETWEEN 25 AND 35 , 1, 0 ) ) AS ag_B, 
COUNT( IF( TIMESTAMPDIFF(YEAR, dob, NOW()) BETWEEN 35 AND 50 , 1, 0 ) ) AS ag_C,
COUNT( IF( TIMESTAMPDIFF(YEAR, dob, NOW()) >50, 1, 0 ) ) AS ag_D 
FROM  emp GROUP BY age_group

但是我无法得到想要的输出。谁能帮我解决这个问题？

Answer 1

试试这个

SELECT Case 
when TIMESTAMPDIFF(YEAR, dob, NOW()) < 25 then '<25'
when TIMESTAMPDIFF(YEAR, dob, NOW()) between 25 and 35 then '25-35'
when TIMESTAMPDIFF(YEAR, dob, NOW()) between 36 and 50 then '36-50'
else '>50' END AS age_group, 
sum(if(gender='Male', 1, 0)) as Male, 
sum(if(gender='Female', 1, 0)) as Female, 
COUNT(1) as total FROM emp 
GROUP BY Case 
when TIMESTAMPDIFF(YEAR, dob, NOW()) < 25 then '<25'
when TIMESTAMPDIFF(YEAR, dob, NOW()) between 25 and 35 then '25-35'
when TIMESTAMPDIFF(YEAR, dob, NOW()) between 36 and 50 then '36-50'
else '>50' END;