收藏私塾在线
 

欢迎您来到私塾在线网!   

请登录! 

威尼斯人线上赌博注册 

交流首页 » 数据库 »能不能帮我优化下这个SQL 谢谢啦  XML
发表人 内容
成长wiing
[头像]

交流经验:
总积分:107
级别:普通会员
注册时间: 2011-11-09
文章: 2
离线

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 

成长wiing
[头像]

交流经验:
总积分:107
级别:普通会员
注册时间: 2011-11-09
文章: 2
离线

实在是跑着太慢了 帮优化下 谢谢啦
woaijiadanoo
[头像]

交流经验:
总积分:60194
级别:VIP5
注册时间: 2011-10-19
文章: 201
离线

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)

这篇文章被编辑了 1 次. 最近一次更新是在 2011-11-09 16:10:39



个性签名:没有色彩的生活中,容易失去寻找快乐的动力
woaijiadanoo
[头像]

交流经验:
总积分:60194
级别:VIP5
注册时间: 2011-10-19
文章: 201
离线

试一下吧 我这没有表和数据,所以没测,不过应该没问题


个性签名:没有色彩的生活中,容易失去寻找快乐的动力
zhanhuidreaming
[头像]

交流经验:
总积分:253
级别:普通会员
注册时间: 2011-11-30
文章: 1
离线

恩,厉害!



个性签名:7*24服务
 
交流首页 » 数据库
前往:   

关于我们 | 联系我们 | 用户协议 | 私塾在线服务协议 | 版权声明 | 隐私保护

版权所有 Copyright(C)2009-2012 私塾在线学习网

博聚网
博聚网