select outsumqty=(select sum(c.qty) from t_str_partinoutitem as c where c.pno=a.pno and c.partver=a.partver and c.lot=a.lot),
c.mustqty-(select sum(c.qty) from t_str_partinoutitem as c where c.pno=a.pno and c.partver=a.partver and c.lot=a.lot) as outsumfrom ....怎么提高效率,最好用一条语句
c.mustqty-(select sum(c.qty) from t_str_partinoutitem as c where c.pno=a.pno and c.partver=a.partver and c.lot=a.lot) as outsumfrom ....怎么提高效率,最好用一条语句
from
(select outsumqty=(select sum(c.qty) from t_str_partinoutitem as c where c.pno=a.pno and c.partver=a.partver and c.lot=a.lot), mustqty from .... ) aa
c.mustqty-sum(b.qty) as outsum,c.returnbadqty1,c.returnbadqty2,a.re,a.id,a.unit,a.lot,a.weight
from t_str_partinoutitem as a left join t_prd_mowkbody as c on a.mono=c.mowkno and a.pno=c.pno and a.partver=c.partver
left join t_eng_partno as d on a.pno=d.pno
left join t_str_partinoutitem as b on b.pno=a.pno and b.partver=a.partver and b.lot=a.lot
where a.mono<>'' and a.inno='OM10120002'
group by a.mono,a.pno,a.partver,d.pname,c.mustqty,c.returnbadqty1,c.returnbadqty2 ,a.qty,a.re,a.id,a.unit,a.lot,a.weight
上面查询改成下面的 哪种要好一点, 感觉写得不好,哪位高手指点一下,怎么改进?
select sum(c.qty) from t_str_partinoutitem as c where c.pno=a.pno and c.partver=a.partver and c.lot=a.lot
变成一个表
select pno,partver,lot,sum(qty) sumQty from t_str_partinoutitem group by pno,partver,lot
现与其它通过pno,partver,lot关联!select oursumqty=c.sumqty,mustqty-c.sumqty outnum
from a
inner join
(select pno,partver,lot,sum(qty) sumQty from t_str_partinoutitem group by pno,partver,lot) c
on c.pno=a.pno and c.partver=a.partver and c.lot=a.lot
with ttt as
(
select sum(c.qty) qty from t_str_partinoutitem as c where c.pno=a.pno and c.partver=a.partver and c.lot=a.lot
)
Select outsumqty=qty,mustqty-qty
select a.mono,a.pno,a.partver,d.pname,c.mustqty,a.qty,outsumqty=sum(b.qty),
c.mustqty-sum(b.qty) as outsum,c.returnbadqty1,c.returnbadqty2,a.re,a.id,a.unit,a.lot,a.weight
from
( select mono,pno,partver,qty,re,id,unit,lot,weight
from t_str_partinoutitem where a.mono<>'' and a.inno='OM10120002' ) as a
left join t_prd_mowkbody as c on a.mono=c.mowkno and a.pno=c.pno and a.partver=c.partver
left join t_eng_partno as d on a.pno=d.pno
left join t_str_partinoutitem as b on b.pno=a.pno and b.partver=a.partver and b.lot=a.lot
group by a.mono,a.pno,a.partver,d.pname,c.mustqty,c.returnbadqty1,c.returnbadqty2 ,a.qty,a.re,a.id,a.unit,a.lot,a.weight
from(
select sum(c.qty) as outsumqty
from t_str_partinoutitem as c
where c.pno=a.pno and c.partver=a.partver and c.lot=a.lot
)