时间戳

首页 > 解决方案 > 如何使用复合键连接多个表

sql - 如何使用复合键连接多个表

问题描述

我有一个带有两列主键的基表和两个带有两列外键（一对多）的表。基表包含两行，每 4 行的第二行和第三行具有相同的键。我试图加入所有表格，但结果翻了一番。你能帮我写只返回 4 行的 select 吗？

select *
from base
         join joined_first jf on base.column_a = jf.column_a and base.column_b = jf.column_b
         join joined_second js on base.column_a = js.column_a and base.column_b = js.column_b;

预期的

a1  b1  c1  a1  b1  d11 e11
a1  b1  c1  a1  b1  d12 e12
a1  b2  c1  a1  b2  d21 e21
a1  b2  c1  a1  b2  d22 e22


create table base
(
    column_a       varchar(20) not null,
    column_b       varchar(20) not null,
    column_c       varchar(20),
    primary key (column_a, column_b)
);

create table joined_first
(
    column_a           varchar(20) not null,
    column_b           varchar(20) not null,
    column_d           varchar(20),
    foreign key (column_a, column_b) references base (column_a, column_b)
);

create table joined_second
(
    column_a           varchar(20) not null,
    column_b           varchar(20) not null,
    column_e           varchar(20),
    foreign key (column_a, column_b) references base (column_a, column_b)
);

insert into base (column_a, column_b,column_c)
values  ('a1', 'b1', 'c1'),
        ('a1', 'b2', 'c1');

insert into joined_first (column_a, column_b, column_d)
values ('a1', 'b1', 'd11'),
       ('a1', 'b1', 'd12'),
       ('a1','b2','d21'),
       ('a1','b2','d22');

insert into joined_second (column_a, column_b, column_e)
values ('a1', 'b1', 'e11'),
       ('a1', 'b1', 'e12'),
       ('a1','b2','e21'),
       ('a1','b2','e22');

标签： sql

解决方案

如果我理解正确，您想“堆叠”两个表中的列。为此，您可以生成一个序列号并将其用于join：

select *
from base b join
     (select jf.*,
             row_number() over (partition by column_a, column_b order by column_d) as seqnum
      from joined_first jf
     ) jf
     on b.column_a = jf.column_a and
        b.column_b = jf.column_b join
     (select js.*,
             row_number() over (partition by column_a, column_b order by column_e) as seqnum
      from joined_second js
     )js
     on b.column_a = js.column_a and
        b.column_b = js.column_b and
        jf.seqnum = js.seqnum;

这是一个 db<>fiddle。

推荐阅读