有条件的左外联接(位置，顺序)?

本文介绍了有条件的左外联接(位置，顺序)?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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