lirong2008wf
lirong2008wf
2011-11-04 11:14

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

已采纳

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

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • iteye_8576 iteye_8576 10年前

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

    点赞 评论 复制链接分享