postgresql LEFT OUTER JOIN 有条件（where，order by）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7015839/
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
LEFT OUTER JOIN with conditions (where, order by)?
提问by fro_oo
I'm working on a Rails3 project with MySql (local) and Postgresql (Heroku) databases.
I need to put conditions on a LEFT OUTER JOIN but I don't know how.
我正在使用 MySql（本地）和 Postgresql（Heroku）数据库开发 Rails3 项目。
我需要在 LEFT OUTER JOIN 上设置条件，但我不知道如何。
I have 2 tables TRAININGS and TRAINING_HISTORIES.
I want to retrieve all the records of TRAININGS and add the last valid (aka finished) associated record of TRAINING_HISTORIES.
我有 2 个表 TRAININGS 和 TRAINING_HISTORIES。
我想检索 TRAININGS 的所有记录并添加 TRAINING_HISTORIES 的最后一个有效（又名完成）关联记录。
table TRAININGS
餐桌培训
id name order_by
5 A 1
6 B 2
7 C 3
table TRAINING_HISTORIES
表 TRAINING_HISTORIES
id training_id finished_at score
43 5 2011-06-06 10
44 5 null null
45 6 2011-07-07 11
46 6 2011-08-08 14
47 6 null null
48 6 2011-09-09 18
49 6 null null
50 7 null null
51 7 2011-10-10 19
Here's my SQL query :
这是我的 SQL 查询：
SELECT tc.id, tc.name, tc.order,
th.id as history_id, th.finished_at, th.score
FROM trainings tc
LEFT OUTER JOIN training_histories th ON th.training_id = tc.id
WHERE tc.id > 4
AND tc.id < 8
GROUP BY tc.id
ORDER BY tc.order_by ASC, tc.id ASC
RESULTS I HAVE :
结果我有：
id name order history_id finished_at score
5 A 1 43 2011-06-06 10
6 B 2 45 2011-07-07 11
7 C 3 50 null null
- The query retrieve the first training_history for each join
- 查询检索每个连接的第一个 training_history
RESULTS I NEED :
我需要的结果：
id name order history_id finished_at score
5 A 1 43 2011-06-06 10
6 B 2 48 2011-09-09 18
7 C 3 51 2011-10-10 19
- In this scenario : it's the last finished training_history who's retrieved...
- 在这种情况下：这是检索到的最后一个完成的 training_history...
Any suggestions really appreciated !
任何建议真的很感激！
Thank you
谢谢
EDIT: If someone can answer on the Rails part, it could be great too ;-)
How to Convert SQL Query to Rails Active Record Query?
编辑：如果有人可以回答 Rails 部分，那也很棒；-)
如何将 SQL 查询转换为 Rails Active Record 查询？
回答by everton
Try this Query, it would give you each training and the most recent training history for each one:
试试这个查询，它会给你每一次训练和每一次的最近训练历史：
SELECT tc.id, tc.name, tc.order,
th.id as history_id, th.finished_at, th.score
FROM trainings tc
LEFT OUTER JOIN training_histories th ON th.training_id = tc.id
and th.id =
(SELECT th1.id from training_histories th1 where th1.training_id = tc.id
and th1.finished_at is not null
order by th1.finished_at desc limit 1)
WHERE tc.id > 4
AND tc.id < 8
GROUP BY tc.id
ORDER BY tc.order_by ASC, tc.id ASC
回答by joakimdahlstrom
I'm quite sure you can do
我很确定你能做到
SELECT tc.id, tc.name, tc.order,
th.id as history_id, th.finished_at, th.score
FROM trainings tc
LEFT OUTER JOIN (SELECT id, training_id, MAX(finished_at) as finished_at, score
FROM training_histories GROUP BY training_id) th ON th.training_id = tc.id
WHERE tc.id > 4
AND tc.id < 8
GROUP BY tc.id
ORDER BY tc.order_by ASC, tc.id ASC
回答by Vinay
Also in the where condition check for finished_at not nulland add Order by finished_at DESC
同样在 where 条件检查finished_at not null并添加Order by finished_at DESC
SELECT tc.id, tc.name, tc.order,
th.id as history_id, th.finished_at, th.score
FROM trainings tc
LEFT OUTER JOIN training_histories th ON th.training_id = tc.id
WHERE tc.id > 4
AND tc.id < 8
AND th.finished_at IS NOT NULL
GROUP BY tc.id
ORDER BY tc.order_by ASC, tc.id ASC, th.finished_at DESC
