主页 / dba / 问题 / 249576
Accepted
ThatDataGuy
Asked: 2019-09-26 05:37:31 +0800 CST

如何匹配外连接中的一行？

  • 772

我有一种情况，传入行 (src) 需要与目标表 (tgt) 中的行匹配。但是，对于所有传入的源行，每个目标行只能匹配一次。示例 SQL 如下：

drop table if exists src;
create table src(id int not null, join_key text not null);
insert into src(id, join_key) values
(1,'X'),
(2,'X'),
(3,'X'),
(4,'Y'),
(5,'Y')
;

drop table if exists tgt;
create table tgt(id int not null, join_key text not null);
insert into tgt(id, join_key) values
(10,'X'),
(20,'X'),
(40,'Y')
;

with a as (
    select s.id as src_id,
           s.join_key,
           t.id as tgt_id
    from src s
             left join tgt t on s.join_key = t.join_key
),b as (
    select *,
           row_number() over (partition by src_id order by tgt_id) as rw
    from a
)
     select src_id, tgt_id from b
 where rw = 1
;

这给出：

1,10
2,10
3,10
4,40
5,40

但价值观应该是：

1,10
2,20
3,null
4,40
5,null

我怎样才能做到这一点？

postgresql

1 个回答

  1. Best Answer
    WITH
cte1 AS ( SELECT id, join_key, ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY id) rn
          FROM src ),
cte2 AS ( SELECT id, join_key, ROW_NUMBER() OVER (PARTITION BY join_key ORDER BY id) rn
          FROM tgt )
SELECT cte1.id src_id, cte2.id tgt_id, COALESCE(cte1.join_key, cte2.join_key) join_key
FROM cte1
FULL OUTER JOIN cte2 ON cte1.join_key = cte2.join_key AND cte1.rn = cte2.rn

    小提琴

    • 2

相关问题