讨论常用SQL语句执行效率问题
－－1、下面三个执行计划是一样的，效率也是一样的，常用第一种写法-----
select a.pid,a.pnm,a.smanno,b.snm,b.memo from pro a,user2 b where a.smanno=b.sid
select a.pid,a.pnm,a.smanno,b.snm,b.memo from pro a inner join user2 b on a.smanno=b.sid
select a.pid,a.pnm,a.smanno,b.snm,b.memo from pro a cross join user2 b where a.smanno=b.sid
－－2、下面二个执行计划一样，效率是前者快，固看情况应用：inner、left、right join－－－
select a.pid,a.pnm,a.smanno,b.snm from pro a inner join user2 b on a.smanno=b.sid
select a.pid,a.pnm,a.smanno,b.snm from pro a left join user2 b on a.smanno=b.sid
－－3、下面二个查询结果集一样，执行计划不一样，效率前者快－－
select a.pid,a.pnm,a.smanno,b.snm from pro a left join user2 b on a.smanno=b.sid
select pid,pnm,smanno,(select snm from user2 as b where b.sid=a.smanno) snm from pro a
－－4、下面二个查询结果集一样，执行计划不一样，效率前者快，后者相当于执行两个left join－－
select a.pid,a.pnm,a.smanno,b.snm,b.tel from pro a left join user2 b on a.smanno=b.sid
select pid,pnm,smanno,
(select snm from user2 as b where b.sid=a.smanno) snm,
(select tel from user2 as b where b.sid=a.smanno) tel from pro a
－－5、下面二个查询结果集一样的，执行计划不一样，效率后者快－－
select a.pid,a.pnm,a.smanno,sum(b.num) qty
from pro a,stock b
where a.pid=b.pid
group by a.pid,a.pnm,a.smanno
select a.pid,a.pnm,a.smanno,b.qty
from pro a,(select pid,sum(num) qty from stock group by pid) b
where a.pid=b.pid
－－6、查询结果集一样，效率前者快－－
select a.pid,a.pnm,a.smanno,b.qty,b.ps
from pro a left join (select pid,sum(num) qty,sum(ps) ps from stock group by pid) b
on a.pid=b.pid
select pid,pnm,smanno,
(select sum(num) from stock b where a.pid=b.pid) qty,
(select sum(ps) from stock b where a.pid=b.pid) ps
from pro a
---老SQL：---
declare @mSYS001 varchar(20),@mSYS002 varchar(20)
select @mSYS001 = SYA002 from XT_TPASYA where SYA001 = 'SYS001'
select @mSYS002 = SYA002 from XT_TPASYA where SYA001 = 'SYS002'
select BGA003 as 生产批号,BGA002 as 日期,
(select DAA002 from TPADAA where DAA001 = BGA012) as 班别,
BGA018 as 机号,(select DBA002 from TPADBA where DBA001 = BGA007) as 员工,
RAA008 as 规格,RAA007 as 材质, BGA006 as 生产数量PCS,
isnull((select sum(b.BGA024) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) as 初检前重量KG,
isnull((select sum(b.BGA981) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) as 初检数量KG,
isnull((select sum(b.BGA006) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) as 初检数量PCS,
isnull((select sum(b.BGA023) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) as 初检不良重量KG,
case isnull((select sum(b.BGA024) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0) when 0 then 0
else isnull((select sum(b.BGA023) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0)
/isnull((select sum(b.BGA024) from XT_SGMBGA b where b.BGA003 = a.BGA003 and b.BGA004 = @mSYS002 and b.BGA980 = a.BGA980 ),0)*100 end as [不良率%]
from XT_SGMBGA a,XT_SGMRAA where RAA001 = a.BGA003 and a.BGA004 = @mSYS001
---进行优化后的SQL---
declare @mSYS001 varchar(20),@mSYS002 varchar(20)
select @mSYS001 = SYA002 from XT_TPASYA where SYA001 = 'SYS001'
select @mSYS002 = SYA002 from XT_TPASYA where SYA001 = 'SYS002'
select a.BGA003 as 生产批号,a.BGA002 as 日期,
DAA002 as 班别,
BGA018 as 机号,DBA002 as 员工,
RAA008 as 规格,RAA007 as 材质, BGA006 as 生产数量PCS,
isnull(e.n1,0) as 初检前重量KG,
isnull(e.n2,0) as 初检数量KG,
isnull(e.n3,0) as 初检数量PCS,
isnull(e.n4,0) as 初检不良重量KG,
case isnull(e.n1,0) when 0 then 0 else e.n4/e.n1 end as [不良率%]
from XT_SGMBGA a
left join TPADAA c on DAA001 = BGA012
left join TPADBA d on DBA001 = BGA007
left join (select BGA003,BGA980,sum(BGA024) n1,sum(BGA981) n2,sum(BGA006) n3,sum(BGA023) n4 from XT_SGMBGA
where BGA004=@mSYS002
group by BGA003,BGA980) e on a.BGA003=e.BGA003 and a.BGA980=e.BGA980
cross join XT_SGMRAA b
where b.RAA001 = a.BGA003 and a.BGA004 = @mSYS001
-------------------------
这些是今天作的一个小结，
有不足地方，还请各大侠补充下啦
[解决办法]
晕 回复谢谢分享也能被删除？
[解决办法]
