获得LEFT OUTER JOIN的第一行 [英] Getting first line of a LEFT OUTER JOIN

问题描述

我有3张桌子:

(SELECT DISTINCT ID
FROM IDS)a
LEFT OUTER JOIN
(SELECT NAME, ID
FROM NAMES)b
ON a.ID = b.ID
LEFT OUTER JOIN
(SELECT ADDRESS FROM ADDRESSES
WHERE ROWNUM <2
ORDER BY UPDATED_DATE DESC)c
ON a.ID = c.ID

ID只能有一个名称，但可以有多个地址.我只想要最新的.即使有一个地址，我也认为此查询返回的地址为null，原因是它仅从表中获取第一个地址，然后尝试将其左移至找不到的地址ID.编写此查询的正确方法是什么?

An ID can have only one name but can have multiple addresses. I only want the latest one. This query returns the address as null even when there is an address I guess cause it only fetches the first address from the table and then tries LEFT JOIN it to the ID of addresses which it canno find. What is the correct way of writing this query?

推荐答案

尝试保持DENSE_RANK

数据源:

CREATE TABLE person
    (person_id int primary key, firstname varchar2(4), lastname varchar2(9))
/
INSERT ALL
    INTO person (person_id, firstname, lastname)
         VALUES (1, 'john', 'lennon')
    INTO person (person_id, firstname, lastname)
         VALUES (2, 'paul', 'mccartney')
SELECT * FROM dual;



CREATE TABLE address
    (person_id int, address_id int primary key, city varchar2(8))
/
INSERT ALL
    INTO address (person_id, address_id, city)
         VALUES (1, 1, 'new york')
    INTO address (person_id, address_id, city)
         VALUES (1, 2, 'england')
    INTO address (person_id, address_id, city)
         VALUES (1, 3, 'japan')
    INTO address (person_id, address_id, city)
         VALUES (2, 4, 'london')
SELECT * FROM dual;

查询:

    select  

      p.person_id, p.firstname, p.lastname,

      x.recent_city

    from person p
    left join (

        select person_id,      

            min(city) -- can change this to max(city). will work regardless of min/max

            -- important you do this to get the recent: keep(dense_rank last)

            keep(dense_rank last order by address_id) 
               as recent_city

        from address 
        group by person_id


    ) x on x.person_id = p.person_id

实时测试: http://www.sqlfiddle.com/#!4/7b1c9 /2

并非所有数据库都具有与Oracle KEEP DENSE_RANK窗口化功能相似的功能，您可以改为使用普通窗口化功能:

Not all database has similar functionality with Oracle's KEEP DENSE_RANK windowing function, you can use plain windowing function instead:

select  

  p.person_id, p.firstname, p.lastname,

  x.recent_city, x.pick_one_only

from person p
left join (

    select 

        person_id,      

        row_number() over(partition by person_id order by address_id desc) as pick_one_only,
        city as recent_city

    from address 



) x on x.person_id = p.person_id and x.pick_one_only = 1

实时测试: http://www.sqlfiddle.com/#!4/7b1c9 /48

或使用元组测试，应在不支持窗口功能的数据库上工作:

Or use tuple testing, shall work on databases that doesn't support windowing function:

select  

  p.person_id, p.firstname, p.lastname,

  x.recent_city

from person p
left join (

    select   
        person_id,city as recent_city    
    from address 
    where (person_id,address_id) in

          (select person_id, max(address_id)
           from address
           group by person_id)



) x on x.person_id = p.person_id 

实时测试: http://www.sqlfiddle.com/#!4/7b1c9 /21

尽管并非所有数据库都像前面的代码中那样支持元组测试.您可以改用JOIN:

Not all database supports tuple testing like in the preceding code though. You can use JOIN instead:

select  

  p.person_id, p.firstname, p.lastname,

  x.recent_city

from person p
left join (

    select 

        address.person_id,address.city as recent_city

    from address 
    join 
    (
          select person_id, max(address_id) as recent_id
           from address
           group by person_id
    ) r 
    ON address.person_id = r.person_id
    AND address.address_id = r.recent_id



) x on x.person_id = p.person_id 

实时测试: http://www.sqlfiddle.com/#!4/7b1c9 /24

这篇关于获得LEFT OUTER JOIN的第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！