有条件的左外联接(位置,顺序)? [英] LEFT OUTER JOIN with conditions (where, order by)?
问题描述
我正在使用MySql(本地)和Postgresql(Heroku)数据库进行Rails3项目.
我需要在左外联接上添加条件,但我不知道该怎么做.
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.
我有2张桌子TRAININGS和TRAINING_HISTORIES.
我想检索TRAININGS的所有记录,并添加TRAINING_HISTORIES的最后一个有效(也称为完成)的关联记录.
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.
id name order_by
5 A 1
6 B 2
7 C 3
表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
这是我的SQL查询:
Here's my SQL query :
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
我得到的结果:
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
- 查询检索每个联接的第一个training_history
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
- 在这种情况下:检索到的是最后完成的training_history ...
任何建议都非常感谢!
谢谢
如果有人可以回答Rails部分,那也可能很棒;-)
如何将SQL查询转换为Rails Active Record查询?
If someone can answer on the Rails part, it could be great too ;-)
How to Convert SQL Query to Rails Active Record Query?
推荐答案
尝试此查询,它将为您提供每种培训以及每种培训的最新培训历史:
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
这篇关于有条件的左外联接(位置,顺序)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!