34,837
社区成员




create table A(outbhid varchar(10),xmbhid varchar(10),[type] int)
insert into A select 'L000001','XM000001',0
union all select 'L000002','XM000002',0
union all select 'T000001','L000002',1
create table B(outbhid varchar(10),clbhid nvarchar(10),outnumber int)
insert into B select 'L000002','材料1',10
union all select 'L000002','材料2',18
union all select 'T000001','材料1',-5
go
select t1.outbhid,t2.clbhid,t2.outnumber+isnull(t4.outnumber,0) as outnumbers
from A t1 inner join B t2 on t1.outbhid=t2.outbhid
left join A t3 on t3.outbhid=t1.xmbhid
left join B t4 on t4.outbhid=t3.outbhid
where t1.xmbhid='XM000002' and t1.[type]=0
go
drop table A,B
/*
outbhid clbhid outnumbers
---------- ---------- -----------
L000002 材料1 10
L000002 材料2 18
(2 行受影响)
*/
SELECT B.clbhid,sum(b.outnumber)
FROM B INNER JOIN
(
SELECT outbhid FROM A WHERE Type=0 AND xmbhid='XM000002'
OR (
Type=1 AND EXISTS (
SELECT * FROM A as T
WHERE T.xmbhid=A.outbhid
)
) as Tmp
ON B.outbhid =Tmp.outbhid
GROUP BY B.clbhid
SELECT B.clbhid,sum(b.outnumber)
FROM B INNER JOIN
(
SELECT outbhid FROM A WHERE Type=0 AND xmbhid='XM000002'
OR (
Type=1 AND EXISTS (
SELECT * FROM A as T
WHERE T.xmbhid=A.outbhid
)
) as Tmp
ON B.outbhid =A.outbhid
GROUP BY B.clbhid
SELECT B.clbhid,sum(b.outnumber)
FROM B INNER JOIN A
ON B.outbhid =A.outbhid
WHERE A.xmbhid='XM000002'
OR
EXISTS(
SELECT *
FROM A as T
WHERE A.outbhid=T.xmbhid
AND A.xmbhid='XM000002'
)
GROUP BY B.clbhid
SELECT B.clbhid,sum(b.outnumber)
FROM B INNER JOIN A
ON B.outbhid =A.outbhid
WHERE A.xmbhid='XM000002'
GROUP BY B.clbhid