如果说30-35岁的暂时没有数据但是要显示出来,其总数显示为0 ,请大神指点
select case
when t.nl < 30 then
' 30岁以下(不含30)'
when t.nl >= 30 and t.nl <= 35 then
'30-35'
when t.nl >= 36 and t.nl <= 40 then
'36-40'
when t.nl >= 41 and t.nl <= 45 then
'41-45'
when t.nl >= 46 and t.nl <= 50 then
'46-50'
when t.nl >= 51 and t.nl <= 55 then
'51-55'
when t.nl >= 56 and t.nl <= 60 then
'56-60'
when t.nl >= 61 and t.nl <= 65 then
'61-65'
when t.nl >= 66 and t.nl <= 69 then
'66-69'
when t.nl >= 70 then
'70以上(含70)'
end as lx,
count(1)as sumper
from (select rownum,
t1.zjkid,
t1.zjxm,
(SELECT floor(MONTHS_BETWEEN(
to_date(to_char(sysdate, 'yyyy-MM-dd'),'yyyy-MM-dd'),
to_date(t1.csny, 'yyyy-MM-dd')
) / 12)
from dual) as nl,
t1.lxsj,
t1.gzdw,
t1.whcd,
t1.sxzy,
t1.cyqssj,
(select y.zy from zjk_zy y where y.zylb='0' and y.id=t1.zczy) as zczy,
(select cc.code_name
from unieap.up_codelist_code cc, unieap.up_codelist c
where cc.codelist_id = c.codelist_id
and cc.code = t1.zczydj
and c.code = 'ZCDJ'
and rownum = 1) as zczydj,
(select y.zy from zjk_zy y where y.zylb='1' and y.id=t1.zyfx) as zyfx
from v_gs_pszj t1 where 1=1
) t
group by case
when t.nl < 30 then
' 30岁以下(不含30)'
when t.nl >= 30 and t.nl <= 35 then
'30-35'
when t.nl >= 36 and t.nl <= 40 then
'36-40'
when t.nl >= 41 and t.nl <= 45 then
'41-45'
when t.nl >= 46 and t.nl <= 50 then
'46-50'
when t.nl >= 51 and t.nl <= 55 then
'51-55'
when t.nl >= 56 and t.nl <= 60 then
'56-60'
when t.nl >= 61 and t.nl <= 65 then
'61-65'
when t.nl >= 66 and t.nl <= 69 then
'66-69'
when t.nl >= 70 then
'70以上(含70)'
end
order by lx asc
select case
when t.nl < 30 then
' 30岁以下(不含30)'
when t.nl >= 30 and t.nl <= 35 then
'30-35'
when t.nl >= 36 and t.nl <= 40 then
'36-40'
when t.nl >= 41 and t.nl <= 45 then
'41-45'
when t.nl >= 46 and t.nl <= 50 then
'46-50'
when t.nl >= 51 and t.nl <= 55 then
'51-55'
when t.nl >= 56 and t.nl <= 60 then
'56-60'
when t.nl >= 61 and t.nl <= 65 then
'61-65'
when t.nl >= 66 and t.nl <= 69 then
'66-69'
when t.nl >= 70 then
'70以上(含70)'
end as lx,
count(1)as sumper
from (select rownum,
t1.zjkid,
t1.zjxm,
(SELECT floor(MONTHS_BETWEEN(
to_date(to_char(sysdate, 'yyyy-MM-dd'),'yyyy-MM-dd'),
to_date(t1.csny, 'yyyy-MM-dd')
) / 12)
from dual) as nl,
t1.lxsj,
t1.gzdw,
t1.whcd,
t1.sxzy,
t1.cyqssj,
(select y.zy from zjk_zy y where y.zylb='0' and y.id=t1.zczy) as zczy,
(select cc.code_name
from unieap.up_codelist_code cc, unieap.up_codelist c
where cc.codelist_id = c.codelist_id
and cc.code = t1.zczydj
and c.code = 'ZCDJ'
and rownum = 1) as zczydj,
(select y.zy from zjk_zy y where y.zylb='1' and y.id=t1.zyfx) as zyfx
from v_gs_pszj t1 where 1=1
) t
group by case
when t.nl < 30 then
' 30岁以下(不含30)'
when t.nl >= 30 and t.nl <= 35 then
'30-35'
when t.nl >= 36 and t.nl <= 40 then
'36-40'
when t.nl >= 41 and t.nl <= 45 then
'41-45'
when t.nl >= 46 and t.nl <= 50 then
'46-50'
when t.nl >= 51 and t.nl <= 55 then
'51-55'
when t.nl >= 56 and t.nl <= 60 then
'56-60'
when t.nl >= 61 and t.nl <= 65 then
'61-65'
when t.nl >= 66 and t.nl <= 69 then
'66-69'
when t.nl >= 70 then
'70以上(含70)'
end
order by lx asc
' 30岁以下(不含30)'
when t.nl >= 30 and t.nl <= 35 then
'30-35'
when t.nl >= 36 and t.nl <= 40 then
'36-40'
when t.nl >= 41 and t.nl <= 45 then
'41-45'
when t.nl >= 46 and t.nl <= 50 then
'46-50'
when t.nl >= 51 and t.nl <= 55 then
'51-55'
when t.nl >= 56 and t.nl <= 60 then
'56-60'
when t.nl >= 61 and t.nl <= 65 then
'61-65'
when t.nl >= 66 and t.nl <= 69 then
'66-69'
when t.nl >= 70 then
'70以上(含70)' 把这些数据,单独放在一张表中,表名为 t 查询时,用 left join
sum(case when t.nl > 30 and t.nl <= 40 then 1 else 0 end) 三十到四十,
sum(case when t.nl > 40 and t.nl <= 50 then 1 else 0 end) 四十到五十,
sum(case when t.nl >50 then 1 else 0 end) 五十以上
from
(select trunc(to_char(sysdate,'yyyymmdd')-to_char(substr('341233197612158111',7,8)))/10000 nl
from dual t1)t;
这个是一行显示所有的,你需要列的形式,可以再转化下
with t as (select 0 qs,29 zz,'30岁以下(不含30)' lx from dual
union all select 30 qs,35 zz,'30-35' from dual
union all select 36 qs,40 zz,'36-40' from dual
union all select 41 qs,45 zz,'41-45' from dual
union all select 46 qs,50 zz,'46-50' from dual
union all select 51 qs,55 zz,'51-55' from dual
union all select 56 qs,60 zz,'56-60' from dual
union all select 61 qs,65 zz,'61-65' from dual
union all select 66 qs,69 zz,'66-69' from dual
union all select 70 qs,1000 zz,'70以上(含70)' from dual)
select t.lx,count(1)as sumper
from t,...
where ..nl between t.qs and t.zz
....
group by t.lx;