时间戳

首页 > 解决方案 > 如何将具有相同主键的多个表连接到每个键一行的表中？

sql - 如何将具有相同主键的多个表连接到每个键一行的表中？

问题描述

是）我有的

我有多个带有主键key和值的表。每个键可能存在于所有表中或仅存在于表的子集中。

以下是示例表：

表 1：

钥匙 价值
一种 1
C 3

表 2：

钥匙 价值
一种 10
D 10

表 3：

钥匙 价值
乙 20
D 10

我想要的是

我想以这样一种方式加入这些表，即每个现有键得到一行，每个表得到一列。如果某个表中不存在某个键，null则应假定该键。所以对于上面的例子，我想得到这个结果：

钥匙 选项卡1 选项卡2 选项卡3
一种 1 10 空值
乙 空值 空值 20
C 3 空值 空值
D 空值 10 10

到目前为止我尝试过的

这是我想出的，它有效：

with tab1 as (
    select * from (values('A', 1), ('C', 3))x(key, value)
),
tab2 as (
    select * from (values('A', 10), ('D', 10))x(key, value)
),
tab3 as (
    select * from (values('B', 20), ('D', 10))x(key, value)
)
select
    coalesce(tab1.key, tab2.key, tab3.key) "key",
    tab1.value tab1,
    tab2.value tab2,
    tab3.value tab3
from tab1
full outer join tab2 on tab2.key = tab1.key
full outer join tab3 on tab3.key = coalesce(tab1.key, tab2.key)
order by coalesce(tab1.key, tab2.key, tab3.key)

问题

在上面的例子中，我的查询看起来还不错，但是在实际情况下，我需要连接 6 个表，并且键实际上分布在三列中。这会导致很长的连接语句。第六个表的语句如下所示：

full outer join tab6
    on tab6.key1 = coalesce(tab1.key1, tab2.key1, tab3.key1, tab4.key1, tab5.key1)
    and tab6.key2 = coalesce(tab1.key2, tab2.key2, tab3.key2, tab4.key2, tab5.key2)
    and tab6.key3 = coalesce(tab1.key3, tab2.key3, tab3.key3, tab4.key3, tab5.key3)

从代码质量的角度来看，复制和粘贴调用的激增coalesce有些令人不安。

是否有另一种更简单的方法来获得我想要的结果？

标签： sqlpostgresql

解决方案

3张桌子的解决方案

WITH keys AS (
    SELECT key FROM tab1 UNION
    SELECT key FROM tab2 UNION
    SELECT key FROM tab3 
)
SELECT key, t1.value, t2.value, t3.value
FROM keys AS k
LEFT JOIN tab1 AS t1 USING (key)
LEFT JOIN tab2 AS t2 USING (key)
LEFT JOIN tab3 AS t3 USING (key)

对于下一个表，只需将其添加到 CTEkeys中即可从所有表中获取所有出现的键。然后将此表附加到此 CT​​E 以获取值。

对于 10 个表，它仍然相当紧凑和合乎逻辑，最重要的是，添加更多表很容易：

WITH keys AS (
    SELECT key FROM tab1 UNION
    SELECT key FROM tab2 UNION
    SELECT key FROM tab3 UNION
    SELECT key FROM tab4 UNION
    SELECT key FROM tab5 UNION
    SELECT key FROM tab6 UNION
    SELECT key FROM tab7 UNION
    SELECT key FROM tab8 UNION
    SELECT key FROM tab9 UNION
    SELECT key FROM tab10  
)
SELECT 
    key, 
    t1.value, t2.value, t3.value, t4.value, t5.value, t6.value, t7.value, t8.value, t9.value, t10.value
FROM keys AS k
LEFT JOIN tab1 AS t1 USING (key)
LEFT JOIN tab2 AS t2 USING (key)
LEFT JOIN tab3 AS t3 USING (key)
LEFT JOIN tab4 AS t4 USING (key)
LEFT JOIN tab5 AS t5 USING (key)
LEFT JOIN tab6 AS t6 USING (key)
LEFT JOIN tab7 AS t7 USING (key)
LEFT JOIN tab8 AS t8 USING (key)
LEFT JOIN tab9 AS t9 USING (key)
LEFT JOIN tab10 AS t10 USING (key)

有一个小提琴： https ://www.db-fiddle.com/f/9aa2wUtR2RbQaGKTtXrr1W/1

或在您可以获得所有键和连接值时创建一个视图，或使用 FULL JOIN（请参阅 Lukas 的解决方案）

推荐阅读