不同年龄段的mysql行数

r7knjye2 于 2021-06-20 发布在 Mysql

关注(0)|答案(4)|浏览(229)

我有一个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

但是我不能得到想要的结果。在这个问题上有人能帮我吗？

mysql

来源：https://stackoverflow.com/questions/52036984/mysql-row-count-with-different-age-ranges

4条答案

按热度按时间

lmvvr0a81#

用例和子查询

select   
   case when age_group < 25 then '<25',
   case when age_group >= 25 and age_group < 35 then '25-35',
   case when age_group >= 35 and age_group < 50 then  '35-50',
   case when age_group >= 50 then '>=50' else null end as age_grp,
   sum(case when gender='Male' then 1 end) as Male, 
   sum(case when gender='Female' then 1 end) as Female,
   sum(case when gender in ('Male','Female') then 1 else 0 end) 
   as total     from
    (
     SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS age_group,gender

     FROM  emp 
   ) as t group by age_grp

赞(0）回复(0）举报 2021-06-21

0yg35tkg2#

你可以试试这个：

select emp_no,
       emp_name,
       dob,
       gender,
       case when age < 25 then 1 else 0 end `<25`,
       case when age >= 25 and age < 35 then 1 else 0 end `25-35`,
       case when age >= 35 and age < 50 then 1 else 0 end `35-50`,
       case when age >= 50 then 1 else 0 end `>=50`
from (
    select emp_no,
           emp_name,
           dob,
           gender,
           year(now()) - year(dob) - (date_format(now(), '%m%d') < date_format(dob, '%m%d'))
     age
    from tbl
) a

赞(0）回复(0）举报 2021-06-21

jk9hmnmh3#

试试这个

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;

赞(0）回复(0）举报 2021-06-20

zzwlnbp84#

请尝试以下操作：

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(case when gender='Male' then 1 end) as Male, 
    sum(case when gender='Female' then 1 end) as Female, 
    sum(case when gender in ('Male','Female') then 1 end) as total, 
    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

赞(0）回复(0）举报 2021-06-20

我来回答

不同年龄段的mysql行数

4条答案

相关问题

热门标签

最新问答