请问这样的SQL语句怎么样优化呢？查询时间好久啊

Question

[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]

按地区统计各个阶段收入的个数，查询要好长时间

Answer 1

[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]