有三个表:
porder: po_no, part_no, so_no, po_qty
prtran: pr_no, part_no, re
sorder: so_no, part_no, so_qtypo_qty和so_qty是整数
其他的都是主键原查询是:select * from porder p join prtran r on p.part_no = r.part_no where po_no='';
现要求加上:用po_qty除于按porder里的part_no和so_no查sorder表的so_qty的值。(要一条sql实现)
要求的sql是要放在ireport上的,各位高手请帮忙。小弟我先谢谢了
porder: po_no, part_no, so_no, po_qty
prtran: pr_no, part_no, re
sorder: so_no, part_no, so_qtypo_qty和so_qty是整数
其他的都是主键原查询是:select * from porder p join prtran r on p.part_no = r.part_no where po_no='';
现要求加上:用po_qty除于按porder里的part_no和so_no查sorder表的so_qty的值。(要一条sql实现)
要求的sql是要放在ireport上的,各位高手请帮忙。小弟我先谢谢了
select part_no,so_no,case when sum(so_qty)!=0 then sum(po_qty)/sum(so_qty) end as prt
from porder p
join sorder s on p.partno = s.part_no and p.so_no=s.so_no
where p.po_no='';
select part_no,so_no,case when sum(so_qty)!=0 then sum(po_qty)/sum(so_qty) end as prt
from porder p
join sorder s on p.partno = s.part_no and p.so_no=s.so_no
where p.po_no=''
group by part_no,so_no;
from porder t1 ,prtran t2 ,sorder t3
where t1.part_no = t2.part_no and t2.po_no=''
and t1.part_no = t3.part_noselect t1.*,t2.* ,t3.* , cast(t1.po_qty*1.0 / t3.po_qty as decimal(18,2))
from porder t1 ,prtran t2 ,sorder t3
where t1.part_no = t2.part_no and t2.po_no=''
and t1.part_no = t3.part_no