[code="java"]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
[/code]
按地区统计各个阶段收入的个数，查询要好长时间
[code="sql"]
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 null
group 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 = 5027
connect by prior c.po_id = c.po_parent_id)
[/code]