表A: CODE location wmsloc
L001 sh 09
L002 sh 09
L003 sh 09
L004 sh 09表B: CODE location wmsloc
L001 sh 09
L002 sh 09
L003 sh 09
L005 sh 09整合为:
CODE location wmsloc aqty bqty sumqty
L001 sh 09 1 1 0
L002 sh 09 1 1 0
L003 sh 09 1 1 0
L004 sh 09 1 0 1
L005 sh 09 0 1 1aqty 为1的意思是 表A有L001 bqty 为1 表B有L001
看一看L004 表A有L004 表B没有L004,所以为0
如些类推
L001 sh 09
L002 sh 09
L003 sh 09
L004 sh 09表B: CODE location wmsloc
L001 sh 09
L002 sh 09
L003 sh 09
L005 sh 09整合为:
CODE location wmsloc aqty bqty sumqty
L001 sh 09 1 1 0
L002 sh 09 1 1 0
L003 sh 09 1 1 0
L004 sh 09 1 0 1
L005 sh 09 0 1 1aqty 为1的意思是 表A有L001 bqty 为1 表B有L001
看一看L004 表A有L004 表B没有L004,所以为0
如些类推
if object_id('[A]') is not null drop table [A]
go
create table [A]([CODE] varchar(4),[location] varchar(2),[wmsloc] varchar(2))
insert [A]
select 'L001','sh','09' union all
select 'L002','sh','09' union all
select 'L003','sh','09' union all
select 'L004','sh','09'
GO
if object_id('[B]') is not null drop table [B]
go
create table [B]([CODE] varchar(4),[location] varchar(2),[wmsloc] varchar(2))
insert [B]
select 'L001','sh','09' union all
select 'L002','sh','09' union all
select 'L003','sh','09' union all
select 'L005','sh','09'
GO
---查询---
select
isnull(a.code,b.code) as code,
isnull(a.location,b.location) as location,
isnull(a.wmsloc,b.wmsloc) as wmsloc,
case when a.code is not null then 1 else 0 end as aqty,
case when b.code is not null then 1 else 0 end as bqty,
case when a.code is not null and b.code is not null then 0 else 1 end as sumqty
from a
full join b
on a.code=b.code and a.location=b.location and a.wmsloc=b.wmsloc---结果---
code location wmsloc aqty bqty sumqty
---- -------- ------ ----------- ----------- -----------
L001 sh 09 1 1 0
L002 sh 09 1 1 0
L003 sh 09 1 1 0
L004 sh 09 1 0 1
L005 sh 09 0 1 1(5 行受影响)
DECLARE @b TABLE(code VARCHAR(10),location VARCHAR(10),wmsloc VARCHAR(10))INSERT INTO @a(code,location,wmsloc)
select 'L001','sh','09' union all
select 'L002','sh','09' union all
select 'L003','sh','09' union all
select 'L004','sh','09'INSERT INTO @b(code,location,wmsloc)
select 'L001','sh','09' union all
select 'L002','sh','09' union all
select 'L003','sh','09' union all
select 'L005','sh','09' SELECT t.*,
CASE WHEN a.code = t.code THEN 1 ELSE 0 END AS aqty,
CASE WHEN b.code = T.code THEN 1 ELSE 0 END AS bqty,
CASE WHEN a.code = t.code AND b.code = t.code THEN 0 ELSE 1 END AS sumqty
FROM (SELECT * FROM @a AS a UNION SELECT * FROM @b AS b ) AS t
LEFT JOIN @a AS a ON t.code = a.code
LEFT JOIN @b AS b ON t.code = b.code/*
code location wmsloc aqty bqty sumqty
---------- ---------- ---------- ----------- ----------- -----------
L001 sh 09 1 1 0
L002 sh 09 1 1 0
L003 sh 09 1 1 0
L004 sh 09 1 0 1
L005 sh 09 0 1 1
*/