根据年龄分割查询每个年龄段的人数

本文以两种查询方式，查询每个年龄段的人数。

1.普通的when case 查询

select age, count(*) as num from (SELECT case when age >= 18 and age <= 20 then '1'when age >= 21 and age <= 25 then '2'when age >= 26 and age <= 30 then '3'when age >= 31 and age <= 35 then '4'when age >= 36 and age <= 40 then '5'when age is NULL or age < 18 then '0'WHEN age > 40 then '6'end as age from t_yuefu_v4_user WHERE recordStatus >= 0) aWHERE 1=1group by age

查询的意思也跟SQL一样，简单易懂。

2.ELT（INTERVAL（））查询方式查询

select ifnull(elt(INTERVAL(age, 18,21,26,31,36,40)+1, '<18','18~20','21~25','26~30','31~35','36~40','>=40'),'未输入') as ageRange,count(INTERVAL(age, 18,21,25,30,35,40)) as count from t_yuefu_v4_user  where recordStatus >= 0 group by ageRange;

这种查询方式利用到两个函数，函数的详细解释在Mysql的官方文档可以看到。

ELT方法的官方解释如下：—— [ ELT Function ]

 ELT(N,str1,str2,str3,...)ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');        -> 'ej'mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');        -> 'foo'