SQL2005,新手求助~~查询速度过慢,我应该怎么写SQL文

[jinhai0411]

数据表如下
A 订单流程序号定义 字段:listID,sequence(序号)
B 订单计划 字段:listID,PL(工作组名称),planNum(计划量),prodate(日期)
C 订单数据 字段:listID,PL(工作组名称),proNum(生产量),sequence(序号),prodate(日期)

查询选定日期的订单信息 (还有未考虑的情况?)
1 有订单计划,有订单生产数据
2 有订单计划,无订单数据
3 无订单计划,有订单数据

查询结果
1 ListID (上面3种情况下出现的ListID)
2 PL B.PL/C.PL (出现第三种情况为C.PL)
3 Everyday_PlanNum 当日计划量 (B.planNum 按listID 和 工作组别 查询 )
4 当日生产量(序号为最大MAX(A.sequence) 的数量和sum(C.proNum) )
5 总的生产量(序号为最大MAX(A.sequence) 的数量和sum(C.proNum) )

求助: C表中数据很多,导致查询速度比较慢,
我知道我写的很烂,求批评,求教育.我应该怎么写啊!!只要出来上面五个查询结果,速度要快--当日出现的ListID,工作组别
select distinct ListID,PL,Everyday_PlanNum
from
(
select B.ListID,B.PL,B.planNum as Everyday_PlanNum
from B
where B.prodate='2011-10-24'

union all

select C.ListID,C.PL,0 as Everyday_PlanNum
from c
left join
(
select B.listID,B.PL from B
where B.proDate='2011-10-24'
) planNum on (planNum.ListID=C.ListID and planNum.PL=C.PL)
where C.prodate='2011-10-24'
and planNum.PL is null

) P




--当日订单生产量
select C.ListID,C.PL,sum(C.ProNum) as Everyday_ProNum
from C
inner join
(
select A.ListID,max(A.sequence) as sequence
from A group by A.ListID
) SelSectionNo on (SelSectionNo.ListID=C.ListID and SelSectionNo.sequence=C.sequence)
where C.ProDate='2011-10-24'
group by C.ListID,C.LineCode




--总的生产量
select C.ListID,C.PL,sum(C.proNum) as Sum_ProNum
from C
inner join
(
select A.ListID,max(A.sequence) as sequence
from A group by A.ListID
) SelSectionNo on (SelSectionNo.ListID=C.ListID and C.sequence=SelSectionNo.sequence)
inner join
(
select distinct ListID
from
(
select B.ListID from B where B.prodate='2011-10-24'

union all

select C.ListID from C where C.prodate='2011-10-24'
) B
) F on (C.ListID=C.ListID)
group by C.ListID,C.LineCode

[让你望见影子的墙]

貌似都可以直接连接，不用中间的表

select A* from A join B on

[owen_0075]

帮顶。。

[中国风]

第一段改為

SELECT 
DISTINCT 	B.ListID,B.PL,ISNULL(B.planNum,0)  AS Everyday_PlanNum
FROM C
LEFT JOIN B ON b.ListID=c.ListID AND b.PL=c.PL and B.proDate='2011-10-24'

[中国风]

作组别
select distinct ListID,PL,Everyday_PlanNum  
from  
  (
  select B.ListID,B.PL,B.planNum as Everyday_PlanNum
  from B
  where B.prodate='2011-10-24'

  union all  

  select C.ListID,C.PL,0 as Everyday_PlanNum
  from c
  left join  
  (
  select B.listID,B.PL from B  
  where B.proDate='2011-10-24'
  ) planNum on (planNum.ListID=C.ListID and planNum.PL=C.PL)
  where C.prodate='2011-10-24'
  and planNum.PL is null

改改:

SELECT 
DISTINCT     C.ListID,C.PL,ISNULL(B.planNum,0)  AS Everyday_PlanNum
FROM C
LEFT JOIN B ON b.ListID=c.ListID AND b.PL=c.PL and B.proDate='2011-10-24'

[--小F--]

1.
select
 * 
from
 a join b 
on
 a.listID=b.listID 
and
 a.listID=c.listID
and
 b.prodate='2011-10-24'
and
 C.ProDate='2011-10-24'   

[中国风]

--总的生产量
select C.ListID,C.PL,sum(C.proNum) as Sum_ProNum
from C
inner join  
  (
  select A.ListID,max(A.sequence) as sequence
  from A group by A.ListID
  ) SelSectionNo on (SelSectionNo.ListID=C.ListID and C.sequence=SelSectionNo.sequence)
 WHERE 
 EXISTS (SELECT 1 FROM B WHERE prodate='2011-10-24' AND ListID=c.ListID)
 or 
 EXISTS(SELECT 1 FROM C AS d WHERE d.ListID=c.ListID AND d.prodate='2011-10-24')

[--小F--]

select
 * 
from
 a left join b 
on
 a.listID=b.listID 
left join c on
 a.listID=c.listID
and
 b.prodate='2011-10-24'
and
 C.ProDate='2011-10-24' 

[--小F--]

--当日订单生产量
select C.ListID,C.PL,sum(C.ProNum) as Everyday_ProNum
from C   
inner join   
  (
  select A.ListID,max(A.sequence) as sequence  
  from A group by A.ListID
  ) SelSectionNo on (SelSectionNo.ListID=C.ListID and SelSectionNo.sequence=C.sequence)
where C.ProDate='2011-10-24'   
group by C.ListID,C.LineCode


---这个真的已经比较简单了