全国各省市不同年龄段人数统计sql

根据人员信息表中的年龄和地址统计出各省市不同年龄段的人数

人员信息表中年龄字段是String型，年龄不全为整数（有3.5岁这样的）

将String型转化为浮点型：CAST(age as FLOAT)

年龄字段中有空值和 ' '，要排除：where mp.age is not null and mp.age !=''

将地址与省市名用like做模糊查询

两表中不同字段模糊查询：mp.addr like '%'||p.name||'%'

剩下就是一些count,sum,group by ,order by的组合了

SQL语句如下：

select a.name,a.age_group,sum(a.count) from
(select p.name ,
case
when CAST(age as FLOAT)>=0.0 and CAST(age as FLOAT)<1.0 then 'age_0_1_no'
when CAST(age as FLOAT)>=1.0 and CAST(age as FLOAT)<3.0 then 'age_1_3_no'
when CAST(age as FLOAT)>=3.0 and CAST(age as FLOAT)<6.0 then 'age_3_6_no'
when CAST(age as FLOAT)>=6.0 and CAST(age as FLOAT)<12.0 then 'age_6_12_no'
when CAST(age as FLOAT)>=12.0 and CAST(age as FLOAT)<18.0 then 'age_12_18_no'
else 'age_18_1000' end
age_group, count(*) from Person mp,province p
where mp.age is not null and mp.age !='' and mp.addr like '%'||p.name||'%' 
group by mp.age,p.name order by p.name) a
GROUP BY a.name,a.age_group order by a.name

查询结果：