oracle 获取 LEFT OUTER JOIN 的第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10545098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting first line of a LEFT OUTER JOIN
提问by Eosphorus
I have 3 tables:
我有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
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?
一个 ID 只能有一个名称，但可以有多个地址。我只想要最新的。即使有一个地址，我猜这个查询也会将地址返回为空，因为它只从表中获取第一个地址，然后尝试将它左连接到它找不到的地址的 ID。编写此查询的正确方法是什么？
回答by Michael Buen
Try KEEP DENSE_RANK
尝试保持 DENSE_RANK
Data source:
数据源：
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;
Query:
询问：
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
Live test: http://www.sqlfiddle.com/#!4/7b1c9/2
现场测试：http: //www.sqlfiddle.com/#!4/7b1c9/2
Not all database has similar functionality with Oracle's KEEP DENSE_RANK windowing function, you can use plain windowing function instead:
并非所有数据库都具有与 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
Live test: http://www.sqlfiddle.com/#!4/7b1c9/48
现场测试：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
Live test: http://www.sqlfiddle.com/#!4/7b1c9/21
现场测试：http: //www.sqlfiddle.com/#!4/7b1c9/21
Not all database supports tuple testing like in the preceding code though. You can use JOIN instead:
不过，并非所有数据库都像前面的代码一样支持元组测试。您可以使用 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
Live test: http://www.sqlfiddle.com/#!4/7b1c9/24
回答by Justin Cave
You can use the analytic function RANK
您可以使用解析函数 RANK
(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 ,
rank() over (partition by id
order by updated_date desc) rnk
FROM ADDRESSES) c
ON ( a.ID = c.ID
and c.rnk = 1)
回答by Neville
Without having access to any database at the moment, you should be able to do
此刻无需访问任何数据库，您应该能够做到
(SELECT DISTINCT ID
FROM IDS) a LEFT OUTER JOIN
(SELECT NAME, ID
FROM NAMES)b ON a.ID = b.ID LEFT OUTER JOIN
(SELECT TOP 1 ADDRESS
FROM ADDRESSES
ORDER BY UPDATED_DATE DESC) c ON a.ID = c.ID
As you might see, the "TOP 1" at 'Address' will only return the first row of the result set.
Also, are you sure that a.ID and c.ID is the same?
I would imagine you need something like .... c ON a.ID = c.AddressID
If not, i'm not entirely sure how you link multiple addresses to a single ID.
如您所见，“地址”中的“TOP 1”将仅返回结果集的第一行。另外，你确定a.ID和c.ID是一样的吗？
我想你需要像 .... c ON a.ID = c.AddressID 如果不是，我不完全确定你如何将多个地址链接到一个 ID。
回答by rajath m.d.
(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, ROWNUMBER() OVER(PARTITON BY ID ORDER BY UPDATED_DATE DESC) RN
FROM ADDRESSES
)c
ON a.ID = c.ID
where c.RN=1
