如果說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
uj5u.com熱心網友回復：
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）'
把這些資料，單獨放在一張表中，表名為 t
查詢時，用 left join
uj5u.com熱心網友回復：
select sum(case when t.nl < 30 then 1 else 0 end) 三十,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;
這個是一行顯示所有的，你需要列的形式，可以再轉化下
uj5u.com熱心網友回復：
提前加上countuj5u.com熱心網友回復：
uj5u.com熱心網友回復：
分段部分（分段名稱、范圍）用with陳述句就可以（如果是固定，不固定就放表里）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;
uj5u.com熱心網友回復：
5樓正解，做一個年齡段維度基礎表與你查詢出來的結果進行left join.轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109213.html
標籤：開發
上一篇：求助！可不可以把一個用戶的表和視圖復制到另一個用戶？
下一篇：SQL問題處理。