以下のような2つのテーブル(user, user_log)があり、userテーブルに紐づく最新のログ1件をjoinして取得したい。
例えば、ユーザに紐づく直近のアクションログだけをjoinしたい場合に使えるかも。
以下、サンプル。
user table
mysql> select * from user; +----+-------+---------------------+---------------------+ | id | name | created_at | updated_at | +----+-------+---------------------+---------------------+ | 1 | user1 | 2017-02-13 12:48:07 | 2017-02-13 12:48:07 | | 2 | user2 | 2017-02-13 12:48:13 | 2017-02-13 12:48:13 | | 3 | user3 | 2017-02-13 12:48:16 | 2017-02-13 12:48:16 | +----+-------+---------------------+---------------------+
user_log table
mysql> select * from user_log; +----+---------+--------+---------------------+ | id | user_id | action | created_at | +----+---------+--------+---------------------+ | 1 | 1 | 1 | 2017-02-13 12:48:59 | | 2 | 1 | 2 | 2017-02-13 12:49:09 | | 3 | 1 | 3 | 2017-02-13 12:49:13 | | 5 | 2 | 1 | 2017-02-13 13:27:11 | | 6 | 2 | 2 | 2017-02-13 13:27:17 | | 7 | 3 | 1 | 2017-02-13 13:27:22 | +----+---------+--------+---------------------+
SQL
mysql> SELECT user.id, user.name, log.action, log.created_at AS log_datetime -> FROM user -> JOIN user_log AS log ON log.id = -> ( -> SELECT max(log_tmp.id) AS id -> FROM user_log AS log_tmp -> WHERE log_tmp.user_id = user.id -> ) AND log.user_id = user.id; +----+-------+--------+---------------------+ | id | name | action | log_datetime | +----+-------+--------+---------------------+ | 1 | user1 | 3 | 2017-02-13 12:49:13 | | 2 | user2 | 2 | 2017-02-13 13:27:17 | | 3 | user3 | 1 | 2017-02-13 13:27:22 | +----+-------+--------+---------------------+
これでそれぞれのユーザー情報に、user_logの最新のログ1件(action, log_datetime)がjoinできる。