枚举并关联表以避免笛卡尔积连接的通用SQL实现

需求说明 #

需要从一组表中提取全部数据，将附属表的内容列为单独的列。以下用州、参议员、众议员的场景做类比（实际场景中需关联十几次同一张表的不同子集）：

示例表结构 #

states（州表）

senators（参议员表）

representatives（众议员表）

期望结果 #

得到包含州、参议员、众议员列的结果表：

当前实现的问题 #

我写出的SQL如下：

select states.name state, sens.name senator, reps.name representative
from states
left join
( select state_code, name
  , row_number() over (partition by state_code order by name) rn
  from representatives
) reps on reps.state_code = states.code 
full outer join 
( select state_code, name
  , row_number() over (partition by state_code order by name) rn
  from senators
) sens on sens.state_code = states.code and sens.rn = reps.rn

（注：之前发布过连接顺序颠倒的错误版本）

这段代码能运行，但依赖于先连接行数更多的众议员表。如果某州的众议员表未填充，或行数少于参议员表，代码就会失效。

通用解决方案（扩展至第三张子表） #

在Chris Maurer的指导下，我将方案扩展到包含州长表的场景：

governors（州长表）

使用的SQL如下：

select states.name as state, g.name as governor, s.name as senator, r.name as representative
from states
left join 
(
 (select g.*
  , row_number() over (partition by state_code order by name) as nbr
  from governors g) g
 full outer Join
 (Select s.*
  , row_number() over (partition by state_code order by name) as nbr
  from senators s) s
 on s.state_code=g.state_code and s.nbr=g.nbr
 full outer join
 (select r.*
  , row_number() over (partition by state_code order by name) as nbr
  from representatives r) r
 on r.state_code=coalesce(g.state_code,s.state_code) and r.nbr=coalesce(g.nbr,s.nbr)
) on states.code = coalesce(r.state_code,s.state_code,g.state_code)

最终结果 #

该方案通过coalesce函数处理子表间的关联条件，解决了子表行数不一致或为空的问题，可扩展到更多子表的场景。

内容的提问来源于Stack Exchange，提问作者PhilHibbs