代码之家 › 专栏 › 技术社区 › meriton

Oracle SQL:最多联接一个关联实体

analytics plsql oracle sql

meriton · 技术社区 · 14 年前

我有桌子 Building 和 Address ,其中每一个 建筑物 与0..n关联 Addresses .

我想列出 Buildings 与关联的 地址 . 如果A 建筑物 有几个入口,因此有几个 地址 ,我不在乎显示哪一个。如果A 建筑物 没有已知地址,地址字段应为 null .

这就是,我想要一个像左联接这样的连接,它最多连接每一行。

我如何在OracleSQL中表达这一点?

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

5 回复 | 直到 14 年前

Jeffrey Kemp 14 年前

我会考虑在select子句中查询地址,例如:

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

这个 ROWNUM=1 意思是“如果有的话就买一个,不在乎哪一个”。

这种方法的优点是,只要地址上存在一个合适的索引,它的性能可能会比大多数备选方案更好。一旦找到查询的每个建筑的地址,它将停止查找更多的地址。

这种方法的缺点是,如果您希望地址表中有多个列,则不能这样做——尽管您可以将它们连接到一个字符串中。

OMG Ponies 14 年前

因为您不关心显示多少地址:

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

Michiel Overeem 14 年前

您可以做的是对您加入的地址进行限制。例如,通过要求没有具有较低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)

在这种情况下,每个建筑物最多只能有一个地址。

davek 14 年前

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)

Matthew Eyles 14 年前

梅里顿,

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

理解查询的最佳方法是从最内部的“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”内联视图将一组转换后的地址传递给最终查询。当建筑物和地址之间的关系为1到0..n时,建筑物和“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 (+);

这种方法的主要优点是:

传递任意数量的地址列。
确定性,每次运行时返回完全相同的结果。
不要在最后的查询中放置不适当的复杂性,这肯定比这个更复杂。
“A”内联视图可以很容易地封装在数据库视图中,可以称之为最新的“地址”视图:

create view latest_address (b_id, a_id, addr1, addr2, addr3, c) as
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;

select b.b_id, b.b_code, b.b_name, a.*
 from building b, latest_address a
 where b.b_id = a.b_id (+);

享受吧!
马修