select top 100
pno as 原料编号,
ord_qty as 采购数量,
price as 单价,
(select sum(qty) from tb_in where pno=a.pno) as 累计收货数量,
(select sum(back) from tb_in where pno=a.pno) as 累计退货数量,
(isnull((select sum(qty) from tb_in where pno=a.pno),0)-
isnull((select sum(back) from tb_in where pno=a.pno),0))
*price as 累计付款金额,
ord_qty -
isnull((select sum(qty) from tb_in where pno=a.pno),0) as 采购欠收数量,
(select ..........) as a,
(select ..........) as b,
(select ..........) as c,
(select ..........) as d,
(select ..........) as e,
(select ..........) as f,
(select ..........) as g,
(select ..........) as h,
(select ..........) as i
from tb_po a
where
(isnull((select sum(qty) from tb_in where pno=a.pno),0)-
isnull((select sum(back) from tb_in where pno=a.pno),0))>10000 and
isnull((select sum(back) from tb_in where pno=a.pno),0)=0注:列出实际收货数量(已收数量-已退数量)大于10000,并且退货数量=0的采购记录有时比这更复杂,有10多个子查询,效率无比低,请问大家有没有好的优化方法?
pno as 原料编号,
ord_qty as 采购数量,
price as 单价,
(select sum(qty) from tb_in where pno=a.pno) as 累计收货数量,
(select sum(back) from tb_in where pno=a.pno) as 累计退货数量,
(isnull((select sum(qty) from tb_in where pno=a.pno),0)-
isnull((select sum(back) from tb_in where pno=a.pno),0))
*price as 累计付款金额,
ord_qty -
isnull((select sum(qty) from tb_in where pno=a.pno),0) as 采购欠收数量,
(select ..........) as a,
(select ..........) as b,
(select ..........) as c,
(select ..........) as d,
(select ..........) as e,
(select ..........) as f,
(select ..........) as g,
(select ..........) as h,
(select ..........) as i
from tb_po a
where
(isnull((select sum(qty) from tb_in where pno=a.pno),0)-
isnull((select sum(back) from tb_in where pno=a.pno),0))>10000 and
isnull((select sum(back) from tb_in where pno=a.pno),0)=0注:列出实际收货数量(已收数量-已退数量)大于10000,并且退货数量=0的采购记录有时比这更复杂,有10多个子查询,效率无比低,请问大家有没有好的优化方法?
(select sum(back) from tb_in where pno=a.pno) as 累计退货数量,
都是在tb_in里选,为什么要分开计算呢?select sum(qty),sum(back) 不行吗?
还有条件里
(select) 这种肯定性能不高啊。条件里一般就是 字段 运算符 变量/常量 比如 date>'2012-12-21' ,type=1
select top 100
pno as 原料编号,
ord_qty as 采购数量,
price as 单价,sum(qty) as qty,sum(back) as back,... from tb_po a join tb_in
on pno=a.pno
where ....
group by ...
order by ...---其余的加减乘除运算可以先读表,根据查询结果再计算
sum(send_q) as sended_q
from tb_so a left join tb_ship b on a.sono=b.sono
group by sono,sodate,true_q报错:列名true_q不存在。
sum(send_q) as sended_q
from tb_so a left join tb_ship b on a.sono=b.sono
group by sono,sodate,so_q-cancel_q
象
select sum(qty) from tb_in where pno=a.pno) as 累计收货数量,
(select sum(back) from tb_in where pno=a.pno) as
这样查询的是同一张表,where 条件又相同,可在一个查询中完成
(isnull((select sum(qty) from tb_in where pno=a.pno),0)-
isnull((select sum(back) from tb_in where pno=a.pno),0))>10000 and
isnull((select sum(back) from tb_in where pno=a.pno),0)=0这个条件写得很有水平,不是就等于
isnull((select sum(qty) from tb_in where pno=a.pno),0)>10000
吗分两步吧select pno,isnull(sum(qty),0) as qty,isnull(sum(back),0) as back
into #t
from tb_in
group by pno
having sum(qty) > 10000
select top 100
a.pno as 原料编号,
a.ord_qty as 采购数量,
a.price as 单价,
b.qty as 累计收货数量,
b.back as 累计退货数量,
(b.qty-b.back)*price as 累计付款金额,
a.ord_qty -
b.qty as 采购欠收数量,
... as a,
... as b,
-- 下面自己改
(select ..........) as c,
(select ..........) as d,
(select ..........) as e,
(select ..........) as f,
(select ..........) as g,
(select ..........) as h,
(select ..........) as i
from tb_po a inner join #t b
on a.pno = b.pno