任何帮助都将不胜感激。
这里有两张样品表。
表A:
ID |Name
123|REG
123|ERT
124|REG
124|ACR表B
ID |Name
123|REG
123|WWW
124|REG
124|ADR以下是简单的联接输出,我将在评论中解释我的问题:
*是的--我想要这一排
*不--我不想要这一排
AID|Aname|BID|Bname
123|REG |123|REG --Yes-- Matched-pair for id '123'
123|ERT |123|REG --No--'REG' already had one match. 'ERT' should pair with 'WWW' for id '123'
123|REG |123|WWW --No--The same reason as above
123|ERT |123|WWW --Yes--non-matched pair for id '123'
124|REG |124|REG
124|ACR |124|REG
124|REG |124|ADR
124|ACR |124|ADR我想要的结果:
AID|Aname|BID|Bname
123|ERT |123|WWW
123|REG |123|REG
124|REG |124|REG
124|ACR |124|ADRSQL server 2017。
提前谢谢你。
我的方法(受“推动者”帖子的启发)
;with CTEall as(
select A.id as AID, A.NAME as Aname, b.id as BID,b.NAME as Bname from A
inner join B on A.id = B.id),
match as (
select A.id as AID, A.NAME as Aname, b.id as BID,b.NAME as Bname
from A inner join B on A.id = B.id and A.NAME = B.NAME)
select *
from CTEall
where Aname not in (select Aname from match where AID = BID)
and Bname not in (select Aname from match where BID = AID)
union all
select * from match
order by 1发布于 2019-04-08 19:01:27
通常,当你以不同的方式思考你想要的逻辑时,答案(或者至少是一个答案)变得显而易见。
我是这样想你的逻辑的:
将表A连接到表B,使A.ID=B.ID (总是)和A.Name=B.Name或A.Name在B中没有匹配,而B.Name在A中没有匹配。
这个逻辑在SQL中非常容易表达。
WHERE a.ID=b.ID
AND (
a.Name=b.Name OR (
NOT EXISTS(SELECT * FROM TableB b2 WHERE b2.ID=a.ID AND b2.Name=a.Name)
AND
NOT EXISTS(SELECT * FROM TableA a2 WHERE a2.ID=b.ID AND a2.Name=b.Name)
)
)发布于 2019-04-08 18:51:51
我会这样做:
with
m as ( -- matched rows
select a.id as aid, a.name as aname, b.id as bid, b.name as bname
from table_a a
join table_b b on a.id = b.id and a.name = b.name
),
l as ( -- unmatched "left rows"
select a.id, a.name,
row_number() over(partition by id order by name) as rn
from table_a a
left join table_b b on a.id = b.id and a.name = b.name
where b.id is null
),
r as ( -- unmatched "right rows"
select b.id, b.name,
row_number() over(partition by id order by name) as rn
from table_b b
left join table_a a on a.id = b.id and a.name = b.name
where a.id is null
)
select aid, aname, bid, bname from m
union all
select l.id, l.name, r.id, r.name
from l
join r on r.id = l.id and r.rn = l.rn注意到:这个解决方案可能有点过分,因为当每个ID有多个行时,匹配所有不匹配的行.一些不必要的东西。在每个OP注释中,每个ID总是有一个不匹配的行。
https://stackoverflow.com/questions/55579818
复制相似问题