管理者に対して複数のユーザーが紐づくなど1:多のテーブルから特定のユーザーを抜き出したいケースなどあると思います。
そういう時は、同じテーブル内で自己結合を使うと特定のデータを抜き出すことができて便利です。
今回は自己結合を使った、1:多のテーブルからデータを抽出する方法をご紹介します。
SQLの自己結合は、同じテーブル内の異なるレコードを関連付ける方法です。自己結合を使用することで、テーブルのデータをより詳細に分析できます。
サンプルデータ
例のような1つのadmin_idに対して複数のuser_idが紐づくケースで説明します。
admin_id | user_id | created_at |
---|---|---|
1234 | abcde | 2023-01-03 |
1234 | fghijh | 2023-02-28 |
3456 | s1wert | 2023-03-02 |
3456 | dgruar | 2023-02-21 |
今回のケースのようなテーブルに対し、他のテーブルのadmin_idをkeyにして紐づくuserの状況を確認するとします。
そのまま結合するとuser_idが複数紐付けられてしまうので1つだけに絞りたい時などに今回紹介する自己結合が便利です。
抽出方法
ポイントとしては、抽出したい今回のテーブルに対し条件を絞った今回のテーブルをJOINするだけになります。
今回はcreated_atが最新のuserだけを抜き出すようにします。
SELECT t1.admin_id, t1.user_id, t1.created_at FROM table_name AS t1 INNER JOIN ( SELECT admin_id, MAX(created_at) AS max_date FROM table_name GROUP BY admin_id ) AS t2 ON t1.admin_id = t2.admin_id AND t1.created_at = t2.max_date;
得られる結果はこちらになります。
admin_id | user_id | updated_at |
---|---|---|
1234 | fghijh | @February 28, 2023 |
3456 | s1wert | @March 2, 2023 |
自己結合を使うことで、1:多のテーブルから任意のデータを抽出することができました!
上記のSQLはMAXを使いましたが、比較してやる方法もあります。
SELECT t1.admin_id, t1.user_id, t1.updated_at FROM table_name t1 LEFT JOIN table_name t2 ON t1.admin_id = t2.admin_id AND t1.updated_at < t2.updated_at WHERE t2.admin_id IS NULL;
ぜひ使ってみてください〜!