sql - SQL 连接表首先使用匹配然后是不匹配的值

Question

以下是两张表。我想首先将它们加入完全匹配，然后再加入不匹配的值。

我需要像下面这样的输出。

drop table if exists #C
CREATE TABLE #C(CustNo varchar(100), TranID varchar (100))
insert into #C values

('A','A123'),
('A','A456'),
('A','A586'),
('B','B36')

drop table if exists #D
CREATE TABLE #D(VCustNo varchar(100), VTransID varchar (100))
INSERT INTO #D VALUES
('A','A123'),
('A','A456'),
('A','A555'), 
('A','A858'), 
('C','C469')

标签： sqlsql-server

Answer 1

这有点复杂。您希望并排匹配。然后你想要独立排序的不匹配。我推荐union all两种不同的情况：

select a.custno as custno, a.transId, b.vtransId
from a join
     b
     on a.custno = b.vcustno and a.transId = b.vtransId
union all
select custno, max(TransId), max(vTransId)
from (select coalesce(a.CustNo, b.vCustNo) as custno, a.TransId, b.VTransId, 
             row_number() over (partition by coalesce(a.custno, b.vcustno),
                                             case when a.TransId is null then 1 else 2 end, 
                                             case when b.vTransId is null then 1 else 2 end
                                order by TransId) as seqnum
      from a full join
           b
           on a.custno = b.vcustno and a.transId = b.vtransId
      where a.custno is null or b.vcustno is null
     ) ab
group by custno, seqnum
order by custno;

这是一个 db<>fiddle。

您也可以不使用union all，但我认为上面的内容更容易理解：

select custno, max(TransId), max(vTransId)
from (select coalesce(a.CustNo, b.vCustNo) as custno, a.TransId, b.VTransId, 
             row_number() over (partition by coalesce(a.custno, b.vcustno)
                                order by case when a.TransId is not null and b.vTransId is null then 1
                                              when b.vTransId is null then 2 else 3
                                         end) as seqnum
      from a full join
           b
           on a.custno = b.vcustno and a.transId = b.vtransId
     ) ab
group by custno, seqnum
order by custno;