gpt4 book ai didi

sql - 获取 LEFT OUTER JOIN 的第一行

转载 作者:行者123 更新时间:2023-12-02 05:38:11 24 4
gpt4 key购买 nike

我有 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只能有一个名字,但可以有多个地址。我只想要最新的。此查询将地址返回为空,即使我猜有一个地址也是如此,因为它只从表中获取第一个地址,然后尝试 LEFT JOIN 它到它找不到的地址 ID。编写此查询的正确方法是什么?

最佳答案

尝试KEEP 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 窗口函数类似的功能,您可以改用普通窗口函数:

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


或者使用元组测试,适用于不支持窗口功能的数据库:

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:

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

关于sql - 获取 LEFT OUTER JOIN 的第一行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10545098/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com