sql - 连接和排序 1 :n relationship 中两个表的不同行

Question

我有一个包含列的数据库表 r_event:

event_start (date), 
event_stop (date),
insurance_id (integer) 

和一个包含列的 r_insurance 表:

insurance_id serial primary key,
insurance_name (text)

每项保险都有多个由 insurance_id 链接的事件。

我正在尝试:
SELECT insurance_id, insurance_name - 每个只有 1 个，
并按最大 event_stop排序:
ORDER BY event_stop DESC NULLS LAST -- ??

例子

r_insurance (insurance_id, insurance_name)
1 | rca
2 | casco
3 | itp

r_event (insurance_id, event_start, event_stop)
1 | 12.10.2012 | 27.11.2012
1 | 07.05.2012 | 24.06.2012
2 | 21.01.2013 | 14.02.2013

输出应该是:

1 | casco -- cause it has the event with the biggest event_stop  2 | rca   -- cause it has the 1st event_stop after the biggest event_stop  3 | itc   -- cause it doesn't have events

我编辑了我的初稿，我希望它们按照具有最大 event_stop 和 NULLS LAST 的事件降序排列。

Answer 1

SELECT i.insurance_id, i.insurance_name, max(e.event_stop) latest_stop
FROM   r_insurance i
LEFT   JOIN r_event e USING (insurance_id)
GROUP  BY 1, 2
ORDER  BY latest_stop DESC NULLS LAST;

LEFT JOIN 对于避免丢失 r_insurance 中在 r_event 中没有相关行的行至关重要 - itc在你的例子中。
ORDER BY 确实是 DESC。此外，NULLS LAST 之前没有逗号。

在 PostgreSQL 9.1 中，主键覆盖了 SELECT 列表中表的所有非聚合列，因此您可以简化(更多细节 here ):

SELECT i.insurance_id, i.insurance_name, max(e.event_stop) latest_stop
FROM   r_insurance i
LEFT   JOIN r_event e USING (insurance_id)
GROUP  BY 1
ORDER  BY latest_stop DESC NULLS LAST;

Demo on sqlfiddle.