Возврат совпадающей пары и несовпадающей пары для каждого идентификатора только один раз
Любая помощь будет оценена по достоинству.
У меня есть две таблицы образцов здесь.
Таблица А:
ID |Name
123|REG
123|ERT
124|REG
124|ACR
Таблица Б
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|ADR
SQL сервер 2017.
Заранее спасибо.
Мой подход (вдохновленный постом @The Impaler)
;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
@SalmanA во второй таблице будет только одно несоответствие.
@GenWan Будет ли больше строк с одним и тем же идентификатором из той или иной таблицы?
@alans Нет, строк больше не будет.
Ответы 2
Я бы сделал:
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.
Спасибо за быстрый ответ. Я думаю, что что-то не так после «союза всех». Я не думаю, что вы можете выбрать r.id, r.name в последнем выборе
Виноват. Исправлена последняя строка для использования r вместо m.
Ваш пост вдохновил меня. Я добавлю свой подход к резюме вопроса.
Часто, когда вы думаете о желаемой логике по-другому, ответ (или, по крайней мере, ответ AN) становится очевидным.
Я думаю о вашей логике так:
JOIN Table A to Table B such that A.ID=B.ID (always) AND EITHER A.Name=B.Name OR A.Name doesn't have a Match in B, and B.Name doesn't have a match in 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)
)
)
Вот рабочий пример db<> для этого решения: dbfiddle.uk/….
Пример данных не включает случай нескольких несовпадающих строк на каждой стороне. В этом случае этот ответ создает для них перекрестное соединение.
@TheImpaler, потому что в комментарии спрашивающий говорит: «во второй таблице будет только одно несоответствие».
Угу, не видел такого. +1
Что делать, если во второй таблице есть два несоответствия для одного идентификатора?