SQL 数据表问题，立即解决，立即给分

[qifachen]

表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 1

aqty 为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 行受影响)

[就是just4]

DECLARE @a TABLE(code VARCHAR(10),location VARCHAR(10),wmsloc VARCHAR(10))
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
*/