sql2005 sql文查询速度太慢,新人求助新人求助新人求助

jinhai0411 2011-10-25 01:36:58

/*
数据表如下
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表中数据很多,导致查询速度比较慢,求修改SQL文新人分数不多不好意思 */

--当日出现的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

...全文