求救:查询语句的优化?

Question

我是一个新手,下列语句由于运行时间比较长,3-4个以上客户端(WEB方式)同时运行时就出错。能否优化?
select cl.部门,rd,yd,jd,nd,(ks-xs) as s
from (select 部门,
             'rd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and (编号 in (select 编号 from tb_rz where 日期 between '2007-03-31' AND '2007-03-31'))),
             'yd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and (编号 in (select 编号 from tb_rz where 日期 between '2007-03-01' AND '2007-03-31'))),
             'jd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and (编号 in (select 编号 from tb_rz where 日期 between '2007-01-01' AND '2007-03-31'))),
             'nd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and (编号 in (select 编号 from tb_rz where 日期 between '2007-01-01' AND '2007-03-31')))
      from (select distinct 部门 from tb_bm) as t
      GROUP BY 部门) as dh
   full join
      (SELECT 部门,
              SUM(CASE WHEN 标志 = '0' and 日期1 <= '2007-03-31' THEN 1 ELSE 0 END) AS ks, 
              SUM(CASE WHEN 标志 = '1' and 日期2 <= '2007-03-31' THEN 1 ELSE 0 END) AS xs
       FROM tb_kh
       group BY 部门) cl 
   on dh.部门=cl.部门
ORDER BY cl.部门 DESC表tb_kh约10万条,表tb_rz约500万条,表tb_bm约100条。望各位前辈指教。
另外:表中有关字段均设为聚集索引,环境:win 2003+sql 2000+iis6.0 P4 2.4G 512M

Answer 1

樓主，請說明你的表結構及關係和你所要的結果
單單看你的SQL語句不知道是什么意思

Answer 2

把full join 改为 left join 
把in子查询换为exists

Answer 3

select cl.部门,rd,rd as yd,jd,jd as nd,(ks-xs) as s
from (select 部门,
             'rd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and (编号 in (select distinct 编号 from tb_rz where 日期 between '2007-03-31' AND '2007-03-31'))),
             --'yd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and (编号 in (select 编号 from tb_rz where 日期 between '2007-03-01' AND '2007-03-31'))),             'jd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and (编号 in (select distinct 编号 from tb_rz where 日期 between '2007-01-01' AND '2007-03-31')))
             --'nd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and (编号 in (select 编号 from tb_rz where 日期 between '2007-01-01' AND '2007-03-31')))
      from (select distinct 部门 from tb_bm) as t
      GROUP BY 部门) as dh
   full join
      (SELECT 部门,
              SUM(CASE WHEN 标志 = '0' and 日期1 <= '2007-03-31' THEN 1 ELSE 0 END) AS ks, 
              SUM(CASE WHEN 标志 = '1' and 日期2 <= '2007-03-31' THEN 1 ELSE 0 END) AS xs
       FROM tb_kh
       group BY 部门) cl 
   on dh.部门=cl.部门
ORDER BY cl.部门 DESC

Answer 4

select cl.部门,rd,yd,jd,nd,(ks-xs) as s
from 
(select 部门,
             'rd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and exists (select 1 from tb_rz where 编号=tb_kh.编号 and 日期 between '2007-03-31' AND '2007-03-31')),
             'yd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and exists (select 1 from tb_rz where 编号=tb_kh.编号 and  日期 between '2007-03-01' AND '2007-03-31')),
             'jd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and exists (select 1 from tb_rz where 编号=tb_kh.编号 and  日期 between '2007-01-01' AND '2007-03-31')),
             'nd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and exists (select 1 from tb_rz where 编号=tb_kh.编号 and  日期 between '2007-01-01' AND '2007-03-31'))
      from tb_bm
      GROUP BY 部门
)as dh
left join 
(SELECT 部门,
      SUM(CASE WHEN 标志 = '0' and 日期1 <= '2007-03-31' THEN 1 ELSE 0 END) AS ks, 
      SUM(CASE WHEN 标志 = '1' and 日期2 <= '2007-03-31' THEN 1 ELSE 0 END) AS xs
  FROM tb_kh
 group BY 部门) cl   on dh.部门=cl.部门

Answer 5

rockyljt(江濤):
谢谢你!!
    不好意思没说清楚,
表tb_kh字段:
部门、编号、日期1、日期2、标志等
表tb_rz字段:
部门、编号、日期
我想要的结果是:
1.从表tb_kh中按部门统计出编号在表tb_rz中不同时间段内(4个时间段:日、月、季、年)存在的编号数,
2.从表tb_kh中,在同一时间段内按部门分别统计2种标志的编号数,然后相减得到实际编号数。(表tb_kh中标志为0,则该编号有效,标志为1,则该编号无效)

Answer 6

谢谢各位!
不能把full join 改为 left join 否则会丢失后面的部,把in子查询换为exists,运行时间变化不大。另外想请教各位,库和表有没有那些设置要注意的地方?

Answer 7

更正:
1.从表tb_kh中按部门统计出编号在表tb_rz中不同时间段内(4个时间段:日、月、季、年)存在的编号数.
改为:
1.从表tb_kh中按部门统计出不同的编号在表tb_rz中不同时间段内(4个时间段:日、月、季、年)存在的编号数,

Answer 8

試試這個看：
select cl.部门,rd,yd,jd,nd,(ks-xs) as s
from (select 部门,
'rd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and exists (select top 1 编号 from tb_rz where tb_kh.编号=编号 and 日期 between '2007-03-31' AND '2007-03-31')),
'yd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and exists (select top 1 编号 from tb_rz where tb_kh.编号=编号 and 日期 between '2007-03-01' AND '2007-03-31')),
'jd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and exists (select top 1 编号 from tb_rz where tb_kh.编号=编号 and 日期 between '2007-01-01' AND '2007-03-31')),
'nd'=(select count(distinct 编号) from tb_kh where 部门=t.部门 and exists (select top 1 编号 from tb_rz where tb_kh.编号=编号 and 日期 between '2007-01-01' AND '2007-03-31'))
      from (select distinct 部门 from tb_bm) as t
      GROUP BY 部门) as dh
   full join
      (SELECT 部门,
              SUM(CASE WHEN 标志 = '0' and 日期1 <= '2007-03-31' THEN 1 ELSE 0 END) AS ks, 
              SUM(CASE WHEN 标志 = '1' and 日期2 <= '2007-03-31' THEN 1 ELSE 0 END) AS xs
       FROM tb_kh
       group BY 部门) cl 
   on dh.部门=cl.部门
ORDER BY cl.部门 DESC

Answer 9

为 日期 建立索引。
你可以把这个sql语句中的各个子select部分分别运行，看究竟是哪个造成的速度慢。