SELECT ISNULL(A.产品,B.产品)[产品],ISNULL(A.地点,B.地点)[地点],ISNULL(A.数量,0)-ISNULL(B.数量,0) [数量] from A FULL JOIN B ON A.产品=B.产品 AND A.地点=B.地点
full join 可以解決,但是如果要B表中的數據不存在A表中,則按照你邏輯,計算的數據為負數。如果要轉換為正數,可以使用abs()函數。 另提供一種方法,同樣最後的數據都是為正數,則使用abs()函數。select 产品,地点,sum(数量) as 数量 from ( select 产品,地点,数量 from a union all select 产品,地点,数量*(-1) from b ) as c group by 产品,地点
上面的可以这样写: select 地点,产品,abs(sum(数量)) as 数量 from ( select 产品,地点,数量 from a union all select 产品,地点,数量*(-1) from b ) c group by 产品,地点
select 地点,产品,abs(sum(数量)) as 数量 from ( select 产品,地点,isnull(数量,0) from a union all select 产品,地点isnull(,数量,0) from b ) c group by 产品,地点如上楼所说的,可以为空的转换为0
SELECT ISNULL(a.[产品], b.[产品]) AS [产品] ,ISNULL(a.[地点], b.[地点]) AS [地点] ,ISNULL(a.[数量], 0) AS [数量A] ,ISNULL(b.[数量], 0) AS [数量b] ,ISNULL(a.[数量], 0) - ISNULL(b.[数量], 0) AS [数量a-b] FROM A FULL JOIN ( SELECT 产品 ,地点 ,[数量] = SUM([数量]) FROM B GROUP BY 产品 ,地点 ) AS B ON a.[产品] = b.[产品] AND a.[地点] = b.[地点]
from A FULL JOIN B ON A.产品=B.产品 AND A.地点=B.地点
另提供一種方法,同樣最後的數據都是為正數,則使用abs()函數。select 产品,地点,sum(数量) as 数量 from (
select 产品,地点,数量 from a
union all
select 产品,地点,数量*(-1) from b
) as c
group by 产品,地点
select 地点,产品,abs(sum(数量)) as 数量
from (
select 产品,地点,数量 from a
union all
select 产品,地点,数量*(-1) from b
) c group by 产品,地点
select 地点,产品,abs(sum(数量)) as 数量
from (
select 产品,地点,isnull(数量,0) from a
union all
select 产品,地点isnull(,数量,0) from b
) c group by 产品,地点如上楼所说的,可以为空的转换为0
,ISNULL(a.[地点], b.[地点]) AS [地点]
,ISNULL(a.[数量], 0) AS [数量A]
,ISNULL(b.[数量], 0) AS [数量b]
,ISNULL(a.[数量], 0) - ISNULL(b.[数量], 0) AS [数量a-b]
FROM A
FULL JOIN ( SELECT 产品
,地点
,[数量] = SUM([数量])
FROM B
GROUP BY 产品
,地点
) AS B ON a.[产品] = b.[产品]
AND a.[地点] = b.[地点]