selectt.project_id,b.project_name,sum(casewhent.age='50~59'then1else0end)male,--50~59sum(casewhent.age='60~69'then1else0end)female,--60~69sum(casewhent.age='70~79'then1el...
select t.project_id, b.project_name, sum(case when t.age='50~59' then 1 else 0 end ) male,--50~59 sum(case when t.age='60~69' then 1 else 0 end ) female,--60~69 sum(case when t.age='70~79' then 1 else 0 end ) unknown--70~79 from ( select c.project_id,case length(p.d204a010005) when 18 then trunc((to_char(sysdate, 'yyyyMMdd')-to_char(to_date(substr(p.d204a010005,7,8),'yyyy-MM-dd'),'yyyyMMdd'))/10000) else when 15 then trunc((to_char(sysdate, 'yyyyMMdd')-to_char(to_date(substr(p.d204a010005,7,8),'yyyy-MM-dd'),'yyyyMMdd'))/10000) end as age from ensure_commission c left join d204a01 p on c.commission_id=p.commission_id where c.submit_organ_id like '%%' ) t left join ensure_pro_book b on t.project_id=b.project_idgroup by t.project_id,b.project_name
展开