mysql 按照年龄段分组查询统计

发表于： 2021年5月26日 2021年5月26日
分类： mysql, 数据库
标签： mysql, sql

示例表结构如下：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `city` varchar(40) NOT NULL,
  `age` int(11) DEFAULT NULL
) ;

按照年龄段查询人数：

SELECT
    nld AS '年龄段',
    count(*) AS '人数'
FROM
    (
        SELECT
            CASE

        WHEN age >= 11
        AND age <= 20 THEN
            '11-20'
        WHEN age >= 21
        AND age <= 30 THEN
            '21-30'

        END AS nld
        FROM
            student
    ) a
GROUP BY
    nld

查询结果：

其他示例

示例一

select nnd as '年龄段',count(*) as '人数',sex as '性别' from
(
select
case
when age>=1 and age<=10 then '1-10'
when age>=11 and age<=20 then '11-20'
when age>=21 and age<=30 then '21-30'
when age>=31 and age<=40 then '31-40' else 'other'
end
as nnd,uname,sex from #t
)
a
group by nnd,sex

示例二

Select popSex,popNative,
 Sum(Case When 年龄 <=20 Then 1 Else 0 End) As '[0-20岁(人)]',
 Sum(Case When 年龄 Between 21 And 40 Then 1 Else 0 End) As '[21-40岁(人)]',
 Sum(Case When 年龄 Between 41 And 60 Then 1 Else 0 End) As '[41-60岁(人)]',
 Sum(Case When 年龄 >=61 Then 1 Else 0 End) As '[60岁以上(人)]' 
 From
 (
 SELECT *, ROUND(DATEDIFF(CURDATE(), popBirthday)/365.2422)  AS '年龄' FROM population
 ) s 
 WHERE areaId=53
 GROUP BY popSex,popNative

示例三

SELECT
	CASE
WHEN age IS NULL THEN
	'未知'
WHEN age < 20 THEN
	'小于20岁'
WHEN age >= 20
AND age < 30 THEN
	'20岁到30岁'
WHEN age >= 30
AND age < 40 THEN
	'30岁到40岁'
WHEN age >= 40
AND age < 50 THEN
	'40岁到50岁'
WHEN age >= 50 THEN
	'50岁以上'
END AS 年龄段,
 count(*) AS 人数
FROM
	user
GROUP BY
	CASE
WHEN age IS NULL THEN
	'未知'
WHEN age < 20 THEN
	'小于20岁'
WHEN age >= 20
AND age < 30 THEN
	'20岁到30岁'
WHEN age >= 30
AND age < 40 THEN
	'30岁到40岁'
WHEN age >= 40
AND age < 50 THEN
	'40岁到50岁'
WHEN age >= 50 THEN
	'50岁以上'
END;

运行结果：

示例四

select nld, count(*) number from (
select @age := TIMESTAMPDIFF(YEAR, child_birthday, CURDATE()), 
    case 
    when @age <= 3 then '0-3' 
    when @age <= 6 then '3-6' 
    when @age <= 9 then '6-9' 
    when @age <= 12 then '9-12' 
    else 'other' end as nld from children) t 
group by nld