Oracle SQL：最多加入一个关联实体

Question

我有表 Building 和 Address，其中每个 Building 与 0..n Addresses 关联。

我想列出带有关联地址的建筑物。如果一栋建筑物有多个入口，因此有多个地址，我不在乎显示哪一个。如果建筑物没有已知地址，则地址字段应为null。

也就是说，我想要类似左连接的东西，最多连接每行一次。

我如何在 Oracle SQL 中表达这一点？

PS：我的查询将包括对两个表的相当复杂的限制。因此，我想避免在查询文本中重复这些限制。

Answer 1

我会考虑查询 SELECT 子句中的地址，例如：

SELECT b.*
      ,(SELECT a.text
        FROM   addresses a
        WHERE  a.buildingid = b.id
        AND    ROWNUM=1) as atext
FROM   building b;

ROWNUM=1 意味着“如果有的话就获取一个，不在乎是哪一个”。

这种方法的优点是，只要在addresses.buildingid上存在合适的索引，它的性能可能会比大多数替代方法更好。一旦为所查询的每栋建筑物找到一个地址，它就会停止寻找更多地址。

这种方法的缺点是，如果您想要地址表中的多个列，则不能 - 尽管您可以将它们连接在一起形成一个字符串。

Answer 2

因为您不关心显示多个地址中的哪一个：

Oracle 9i+：

WITH summary AS (
      SELECT b.*,
             a.*,
             ROW_NUMBER() OVER (PARTITION BY b.building_id) rn
        FROM BUILDINGS b
   LEFT JOIN ADDRESSES a ON a.building_id = b.building_id)
SELECT s.*
  FROM summary s
 WHERE s.rn = 1

非子查询因子分解等效项：

SELECT s.*
  FROM (SELECT b.*,
               a.*,
               ROW_NUMBER() OVER (PARTITION BY b.building_id) rn
           FROM BUILDINGS b
      LEFT JOIN ADDRESSES a ON a.building_id = b.building_id) s
 WHERE s.rn = 1

Answer 3

您可以做的是限制您加入的地址。
例如，要求不存在具有较低 ID 的地址：

select *
from building b
left join addresses a on (a.buildingid = b.id)
where not exists (select 1 from addresses a2
                  where a2.buildingid = b.id and a2.id < a.id)

在这种情况下，每个建筑物最多可获得 1 个地址。

Answer 4

select b.*, max(a.id) as aid 
from building b 
left outer join addresses a on (a.buildingid = b.id) 
group by a.buildingid 

或者

select b.*, maxid
from building b 
left outer join 
(
 select buildingid, max(id) as maxid
 from addresses
 group by buildingid 
) a on (a.buildingid = b.id) 

Answer 5

Meriton，

这种方法使用嵌套内联视图。我已经在大数据集上证明了这种方法，它的表现非常好。

理解查询的最好方法是从最里面的“M”内联视图开始。为了调试和清晰起见，我添加了计数。这标识了每个建筑物的最大（即最近？？？）地址ID：

   select maxa.b_id, max(maxa.a_id) a_id, count(*) c
   from address maxa
   group by maxa.b_id;

下一个“A”内联视图使用上面的“M”内联视图来决定要获取哪个地址，然后连接到该地址ID以返回地址字段集：

  select ma.b_id, ma.a_id, ma.addr1, ma.addr2, ma.addr3, m.c
  from address ma, 
     ( select maxa.b_id, max(maxa.a_id) a_id, count(*) c
       from address maxa
       group by maxa.b_id ) m
  where ma.a_id = m.a_id;

上面的“A”内联视图将一组转换后的地址传递给最终查询。 BUILDING 和 ADDRESS 之间的关系是 1 到 0..n，而 BUILDING 和“A”之间的关系是 1 到 0..1，这是一种基本的外连接：

select b.b_id, b.b_code, b.b_name, a.*
 from building b, 
    ( select ma.b_id, ma.a_id, ma.addr1, ma.addr2, ma.addr3, m.c
      from address ma, 
         ( select maxa.b_id, max(maxa.a_id) a_id, count(*) c
           from address maxa
           group by maxa.b_id ) m
      where ma.a_id = m.a_id ) a
 where b.b_id = a.b_id (+);

这种方法的主要优点是：

1. 提供任意数量的地址列。
2. 确定性，每次运行都会返回完全相同的结果。
3. 不会给您的最终查询带来过度的复杂性，最终查询肯定会比这个查询更复杂。
4. “A”内联视图可以轻松封装在数据库视图中，也许可以将其称为 LATEST_ADDRESS 视图：

创建视图latest_address (b_id, a_id, addr1, addr2, addr3, c) as
选择ma.b_id、ma.a_id、ma.addr1、ma.addr2、ma.addr3、mc
 从地址ma， 
    ( 选择 maxa.b_id, max(maxa.a_id) a_id, count(*) c
      从地址 maxa
      按 maxa.b_id 分组) m
 其中 ma.a_id = m.a_id;

选择 b.b_id、b.b_code、b.b_name、a.*
 从 b 楼出发，最新地址 a
 其中 b.b_id = a.b_id (+);

享受吧！
马修