MySQL根据年龄分组查人数

最新推荐文章于 2024-10-15 17:00:00 发布

zwandming

最新推荐文章于 2024-10-15 17:00:00 发布

阅读量450

点赞数 1

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/zwandming/article/details/117924659

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

根据年龄分组查人数

第一种
第二种

第一种

SELECT (CASE
WHEN a.age < 0 THEN '负数'
WHEN a.age IS NULL THEN '未知'
WHEN a.age BETWEEN 0 AND 17 THEN '18岁以下'
WHEN a.age BETWEEN 18 AND 25 THEN '18-25岁'
WHEN a.age BETWEEN 26 AND 35 THEN '26-35岁'
WHEN a.age BETWEEN 36 AND 45 THEN '36-45岁'
WHEN a.age BETWEEN 46 AND 60 THEN '45-60岁'
WHEN a.age >= 61 THEN '60岁以上'
END) AS s,
COUNT(*) 'value' FROM
(SELECT TIMESTAMPDIFF(YEAR,tt_person.birthday,CURDATE()) age FROM tt_person) a
GROUP BY s;

第二种

SELECT (CASE
WHEN a.age IS NULL THEN '未知'
WHEN a.age BETWEEN 18 AND 25 THEN '18-25岁'
WHEN a.age BETWEEN 26 AND 35 THEN '26-35岁'
WHEN a.age BETWEEN 36 AND 45 THEN '36-45岁'
WHEN a.age BETWEEN 45 AND 60 THEN '45-60岁'
WHEN a.age >= 60 THEN '60岁以上'
END) AS s,
COUNT(*) 'value' FROM
(SELECT(YEAR (curdate())- YEAR ( birthday )- 1+ (DATE_FORMAT( birthday, '%m%d' )<=(DATE_FORMAT( curdate(), '%m%d' )))) age FROM tt_person) a GROUP BY s;