按照年龄统计人数

有两种方法从数据库中查询数据

1. 第一种是查询为一个对象（很少用）

select * from
(select nvl(count(id),0) age_twenty_five from TB_LEADER_LIST where age >=0 and 25>age ) a,
(select nvl(count(id),0) age_thirty from TB_LEADER_LIST where age >=25 and 30>age) b,
(select nvl(count(id),0) age_thirty_five from TB_LEADER_LIST where age >=30 and 35>age) c,
(select nvl(count(id),0) age_forty from TB_LEADER_LIST where age >=35 and 40>age) d,
(select nvl(count(id),0) age_forty_five from TB_LEADER_LIST where age >=40 and 45>age) e,
(select nvl(count(id),0) age_fifty from TB_LEADER_LIST where age >=45 and 50>age) f,
(select nvl(count(id),0) age_fifty_five from TB_LEADER_LIST where age >=50 and 55>age) g,
(select nvl(count(id),0) age_sixty from TB_LEADER_LIST where age >=55 and 60>age) x,
(select nvl(count(id),0) age_sixty_five from TB_LEADER_LIST where age >=60 and 65>age) y,
(select nvl(count(id),0) age_hundred from TB_LEADER_LIST where age >=65 and 200>age) z

查询结果如下：

2. 第二种就是查询为list

a. 使用case when的形式

SELECT age_structure, COUNT (age) AS total
    FROM (SELECT age,
                 CASE
                    WHEN age BETWEEN 0 AND 24 THEN '25及以下'
                    WHEN age BETWEEN 24 AND 29 THEN '25~30岁'
                    WHEN age BETWEEN 30 AND 34 THEN '30~35岁'
                    WHEN age BETWEEN 35 AND 39 THEN '35~40岁'
                    WHEN age BETWEEN 40 AND 44 THEN '40~45岁'
                    WHEN age BETWEEN 45 AND 49 THEN '45~50岁'
                    WHEN age BETWEEN 50 AND 54 THEN '50~55岁'
                    WHEN age BETWEEN 55 AND 59 THEN '55~60岁'
                    WHEN age BETWEEN 60 AND 64 THEN '60~65岁'
                    WHEN age BETWEEN 65 AND 150 THEN '65及以上'
                 END
                    AS age_structure
            FROM TB_LEADER_LIST)
GROUP BY age_structure
order by age_structure;

b.使用union all

select '0-25岁' as 年龄段, count(*) as 人数 from TB_LEADER_LIST where  age >=0 and 25>age
union all
select '25-30岁' as 年龄段, count(*) as 人数 from TB_LEADER_LIST where age >=25 and 30>age
union all
select '30-35岁' as 年龄段, count(*) as 人数 from TB_LEADER_LIST where age >=30 and 35>age