它的优化并不咋样呀。看看,我用一条SQL语句测试一下
**********************************************************
select z3.xhid,
z3.钢材根数1 钢材根数1,
(z3.加工数1 - z4.检验不良数1) 良品数1,
z4.不良数1 不良数1,
z3.钢材根数2 钢材根数2,
(z3.加工数2 - z4.检验不良数2) 良品数2,
z4.不良数2 不良数2,
z3.钢材根数3 钢材根数3,
(z3.加工数3 - z4.检验不良数3) 良品数3,
z4.不良数3 不良数3,
z3.钢材根数4 钢材根数4,
(z3.加工数4 - z4.检验不良数4) 良品数4,
z4.不良数4 不良数4,
z3.钢材根数5 钢材根数5,
(z3.加工数5 - z4.检验不良数5) 良品数5,
z4.不良数5 不良数5
from (select z1.xhid,
sum(case z1.bz when '甲' then
z1.gcgs
else
0 end) as 钢材根数1,
sum(case z1.bz when '甲' then
z1.jgs
else
0 end) 加工数1,
sum(case z1.bz when '乙' then
z1.gcgs
else
0 end) as 钢材根数2,
sum(case z1.bz when '乙' then
z1.jgs
else
0 end) 加工数2,
sum(case z1.bz when '丙' then
z1.gcgs
else
0 end) as 钢材根数3,
sum(case z1.bz when '丙' then
z1.jgs
else
0 end) 加工数3,
sum(case z1.bz when '丁' then
z1.gcgs
else
0 end) as 钢材根数4,
sum(case z1.bz when '丁' then
z1.jgs
else
0 end) 加工数4,
sum(z1.gcgs) as 钢材根数5,
sum(z1.jgs) 加工数5
from cxzyrb z1
where (z1.bp = 'A')
and (convert(nchar(7), z1.jgrq, 20) = '2002-04')
group by z1.xhid) as z3
LEFT JOIN (select z2.xhid,
sum(case z2.bfyy when '调整报废' then
0
else
(case z2.bz when '甲' then
z2.bls
else
0 end) end) 检验不良数1,
sum(case z2.bz when '甲' then
z2.bls
else
0 end) as 不良数1,
sum(case z2.bfyy when '调整报废' then
0
else
(case z2.bz when '乙' then
z2.bls
else
0 end) end) 检验不良数2,
sum(case z2.bz when '乙' then
z2.bls
else
0 end) as 不良数2,
sum(case z2.bfyy when '调整报废' then
0
else
(case z2.bz when '丙' then
z2.bls
else
0 end) end) 检验不良数3,
sum(case z2.bz when '丙' then
z2.bls
else
0 end) as 不良数3,
sum(case z2.bfyy when '调整报废' then
0
else
(case z2.bz when '丁' then
z2.bls
else
0 end) end) 检验不良数4,
sum(case z2.bz when '丁' then
z2.bls
else
0 end) as 不良数4,
sum(case z2.bfyy when '调整报废' then
0
else
z2.bls end) 检验不良数5,
sum(z2.bls) as 不良数5
from zlfptjd z2
where (z2.gx = '车削')
and (z2.bp = 'A')
and (convert (nchar (7) , z2.jgrq , 20) = '2002-04') group by z2.xhid) as z4
ON z3.xhid = z4.xhid
**********************************************************
————————————————————————————
批运行开始于 2002-04-25 10:30:23
终止条件 : 最佳运行时间
SQL : 源SQL语句
开始时间 : 2002-04-25 10:30:25
运行时间 : 00:00:00.165
SQL Server运行成本: 0.26869
记录数目 : 37
备注 : 第二次运行时间
SQL : SQL1
开始时间 : 2002-04-25 10:30:31
运行时间 : 00:00:00.305
SQL Server运行成本: 0.293305
记录数目 : 37
备注 : 第二次运行时间
最佳运行时间 (源SQL语句) = 00:00:00.165
——————————————————————————