各位大侠快来求命!!!!!
t1 采购表
po_number item_num po_qty
po07-01235 001 500
po07-01235 002 300
po07-01475 003 1000
t2收货表
po_number item_num in_qty
po07-01235 001 500
po07-01235 002 300
t3 退货表
po_number item_num out_qty
po07-01235 001 100
我要结果是:
po_number item_num po_qty in_qty balance(还有多少未到)
po07-01235 001 500 400 100
po07-01235 002 300 300 0
po07-01475 003 1000 0 1000
只要SQL 语句,谢谢!
[解决办法]
select t1.po_number,t1.item_num,t1.po_qty,t1.in_qty-t2.out_qty,t3.out_qty from
t1 left join t2 on t1.item_num=t2.item_num
join t3 on t2.item_num=t3.item_num
[解决办法]
select distinct t1.po_number,t1.item_num ,t1.po_qty,isnull(t2.in_qty,0) as in_qty,
Convert(int,t1.po_qty)-Convert(int,isnull(t2.in_qty,0) as in_qty) as balance
from ti left join t2 on t1.po_number=t2.po_number left outer join t3 on t1.po_number=t3.po_number
[解决办法]
drop table t1,t2,t3
go
create table t1(po_number varchar(20),item_num varchar(20),po_qty int)
insert into t1
select 'po07-01235 ', '001 ',500
union all select 'po07-01235 ', '002 ',300
union all select 'po07-01475 ', '003 ',1000
create table t2(po_number varchar(20),item_num varchar(20),in_qty int)
insert into t2
select 'po07-01235 ', '001 ',500
union all select 'po07-01235 ', '002 ',300
create table t3(po_number varchar(20),item_num varchar(20),out_qty int)
insert into t3
select 'po07-01235 ', '001 ',100
select t1.po_number,
t1.item_num,
t1.po_qty,
isnull(t2.in_qty,0)-isnull(t3.out_qty,0) as in_qty,
isnull(t1.po_qty,0)-isnull(t2.in_qty,0)+isnull(t3.out_qty,0) as balance
from t1
left join (select po_number,item_num,sum(in_qty) in_qty from t2 group by po_number,item_num)t2 on t1.po_number=t2.po_number and t1.item_num=t2.item_num
left join (select po_number,item_num,sum(out_qty) out_qty from t3 group by po_number,item_num)t3 on t1.po_number=t3.po_number and t1.item_num=t3.item_num
/*
po_number item_num po_qty in_qty balance
-------------------- -------------------- ----------- ----------- -----------
po07-01235 001 500 400 100
po07-01235 002 300 300 0
po07-01475 003 1000 0 1000
（所影响的行数为 3 行）
*/
[解决办法]
SELECT DISTINCT
T1.po_number,
T1.item_num,
T1.po_qty,
CASE T2.po_number WHEN T3.po_number THEN(CASE T2.item_num WHEN T3.item_num THEN T2.in_qty - T3.out_qty ELSE T2.in_qty END) ELSE 0 END AS in_qty,
T1.po_qty - CASE T2.po_number WHEN T3.po_number THEN(CASE T2.item_num WHEN T3.item_num THEN T2.in_qty - T3.out_qty ELSE T2.in_qty END) ELSE 0 END AS balance
FROM
T1 LEFT JOIN T2 ON T1.po_number = T2.po_number AND T1.item_num = T2.item_num,
T3
ORDER BY T1.po_number,T1.item_num
