根据人员信息表中的年龄和地址统计出各省市不同年龄段的人数
人员信息表中年龄字段是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
查询结果: