问题描述
select f.pc_name, f.phc_name, (select count(1) from p_resident c where c.phc_id = f.phc_id and c.pr_income is not null and c.pr_income < 4000) fourqian, (select count(1) from p_resident c where c.phc_id = f.phc_id and c.pr_income is not null and c.pr_income >= 4000 and c.pr_income < 5000) fourfiveqian, (select count(1) from p_resident c where c.phc_id = f.phc_id and c.pr_income is not null and c.pr_income >= 5000 and c.pr_income < 6000) fivesixqian, (select count(1) from p_resident c where c.phc_id = f.phc_id and c.pr_income is not null and c.pr_income >= 6000 and c.pr_income < 7000) sixsevenqian, (select count(1) from p_resident c where c.phc_id = f.phc_id and c.pr_income is not null and c.pr_income >= 7000) sevenqian from (select t.pc_name, a.phc_name, a.phc_id from p_community t, p_home_community a where t.pc_id = a.pc_id and t.pc_id in (select c.po_id from p_organization c start with c.po_id = 5027 connect by prior c.po_id = c.po_parent_id)) f按地区统计各个阶段收入的个数，查询要好长时间
解决方案
select t.pc_name, a.phc_name, c.fourqian, c.fourfiveqian, c.fivesixqian, c.sixsevenqian, c.sevenqian from p_community t, p_home_community a, (select c.phc_id, sum(case when c.pr_income < 4000 then 1 else 0 end) fourqian, sum(case when c.pr_income >= 4000 and c.pr_income < 5000 then 1 else 0 end) fourfiveqian, sum(case when c.pr_income >= 5000 and c.pr_income < 6000 then 1 else 0 end) fivesixqian, sum(case when c.pr_income >= 6000 and c.pr_income < 7000 then 1 else 0 end) sixsevenqian, sum(case when c.pr_income >= 7000 then 1 else 0 end) sevenqian from p_resident c where c.pr_income is not nullgroup by c.phc_id ) c where t.pc_id = a.pc_id and a.pc_id = c.pc_id and t.pc_id in (select c.po_id from p_organization c start with c.po_id = 5027connect by prior c.po_id = c.po_parent_id)