假设有表T3,里面的数据是要减去的,T1 T2 条件不变SELECT ProductID,SUM([Count]) AS [Count] FROM ( SELECT ProductID,[Count] FROM T1 UNION ALL SELECT ProductID,[Count] FROM T2 UNION ALL SELECT ProductID,-1*[Count] FROM T3 ) AS A GROUP BY ProductID
create table #T1 ( ProductID int, [Count] int ) insert into #T1 select 1,10 insert into #T1 select 2,20 insert into #T1 select 3,30 create table #T2 ( ProductID int, [Count] int ) insert into #T2 select 1,15 insert into #T2 select 4,10 insert into #T2 select 1,10create table #T3 ( ProductID int, [Count] int ) insert into #T3 select 1,15 insert into #T3 select 4,10 insert into #T3 select 1,10select isnull(A.productID,isnull(B.ProductId,C.ProductId)) as ProductID ,sum(isnull(A.Count,0) + isnull(B.Count,0)+ isnull(C.Count,0)) as T_Count from #T1 as A full join #t2 as B on A.ProductID=B.ProductID full join #t3 As C on (A.ProductID=C.ProductId or B.productId=C.ProductID) group by A.ProductId,B.ProductID,C.ProductId order by productiD 1 140 2 20 3 30 4 20
SELECT ProductID,SUM([Count]) AS [Count] FROM ( SELECT * FROM tb1 UNION ALL SELECT * FROM tb2 ) AS A GROUP BY ProductID
假设有表T3,里面的数据是要减去的,T1 T2 条件不变SELECT ProductID,SUM([Count]) AS [Count]
FROM (
SELECT ProductID,[Count] FROM T1
UNION ALL
SELECT ProductID,[Count] FROM T2
UNION ALL
SELECT ProductID,-1*[Count] FROM T3
) AS A
GROUP BY ProductID
(
ProductID int,
[Count] int
)
insert into #T1 select 1,10
insert into #T1 select 2,20
insert into #T1 select 3,30
create table #T2
(
ProductID int,
[Count] int
)
insert into #T2 select 1,15
insert into #T2 select 4,10
insert into #T2 select 1,10create table #T3
(
ProductID int,
[Count] int
)
insert into #T3 select 1,15
insert into #T3 select 4,10
insert into #T3 select 1,10select isnull(A.productID,isnull(B.ProductId,C.ProductId)) as ProductID ,sum(isnull(A.Count,0) + isnull(B.Count,0)+ isnull(C.Count,0)) as T_Count from #T1 as A
full join #t2 as B on A.ProductID=B.ProductID
full join #t3 As C on (A.ProductID=C.ProductId or B.productId=C.ProductID)
group by A.ProductId,B.ProductID,C.ProductId
order by productiD
1 140
2 20
3 30
4 20
FROM (
SELECT * FROM tb1
UNION ALL
SELECT * FROM tb2
) AS A
GROUP BY ProductID