親テーブルに紐づく子テーブルから最新のレコードだけを取得する
やりたいこと
親テーブルに紐作く子テーブルレコードで最新IDを持つレコードの情報を取得したい。
やりたいことを具体的にSQLに
usersとpostsを結合して両方のデータを取得する
SELECT users.*, posts.*
FROM users
JOIN posts ON users.user_id = posts.user_id
単純に結合して抽出したレコード
| user_id | username | email | post_id | post.user_id | title | content | created_at |
| --- | ----------- | ---------------- | --- | ------- | ----------- | --------------------------- | ------------------- |
| 1 | John Doe | john@example.com | 1 | 1 | First Post | This is John's first post. | 2023-01-01 12:00:00 |
| 1 | John Doe | john@example.com | 2 | 1 | Second Post | Another post by John. | 2023-02-15 10:30:00 |
| 2 | Jane Smith | jane@example.com | 3 | 2 | Hello World | Jane says hello! | 2023-03-20 08:45:00 |
| 3 | Bob Johnson | bob@example.com | 4 | 3 | MySQL Tips | Bob shares some MySQL tips. | 2023-04-10 15:20:00 |
| 3 | Bob Johnson | bob@example.com | 5 | 3 | New Project | Bob starts a new project. | 2023-05-05 11:00:00 |
上記のレコードをフィルタしてにuser_id毎にpost_idが最大のレコードのみに抽出できるようにしたいです。
最終的に欲しいレコード
| user_id | username | email | post_id | user_id | title | content | created_at |
| ------- | ----------- | ---------------- | ------- | ------- | ----------- | ------------------------- | ------------------- |
| 1 | John Doe | john@example.com | 2 | 1 | Second Post | Another post by John. | 2023-02-15 10:30:00 |
| 2 | Jane Smith | jane@example.com | 3 | 2 | Hello World | Jane says hello! | 2023-03-20 08:45:00 |
| 3 | Bob Johnson | bob@example.com | 5 | 3 | New Project | Bob starts a new project. | 2023-05-05 11:00:00 |
実行環境
-
DB Fiddle - SQL Database Playground
- MySQL 8.0
テーブル作成とテストデータ追加
DDL
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
INSERT INTO users (username, email)
VALUES
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com'),
('Bob Johnson', 'bob@example.com');
CREATE TABLE posts (
post_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
title VARCHAR(100) NOT NULL,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
INSERT INTO posts (user_id, title, content, created_at)
VALUES
(1, 'First Post', 'This is John\'s first post.', '2023-01-01 12:00:00'),
(1, 'Second Post', 'Another post by John.', '2023-02-15 10:30:00'),
(2, 'Hello World', 'Jane says hello!', '2023-03-20 08:45:00'),
(3, 'MySQL Tips', 'Bob shares some MySQL tips.', '2023-04-10 15:20:00'),
(3, 'New Project', 'Bob starts a new project.', '2023-05-05 11:00:00');
抽出に利用したSQL
DML
SELECT users.*, posts.*
FROM users
JOIN (
SELECT user_id, MAX(post_id) as max_id
FROM posts
GROUP BY user_id
) max_posts ON users.user_id = max_posts.user_id
JOIN posts ON max_posts.max_id = posts.post_id;
実行結果
| id | username | email | id | user_id | title | content | created_at |
| --- | ----------- | ---------------- | --- | ------- | ----------- | ------------------------- | ------------------- |
| 1 | John Doe | john@example.com | 2 | 1 | Second Post | Another post by John. | 2023-02-15 10:30:00 |
| 2 | Jane Smith | jane@example.com | 3 | 2 | Hello World | Jane says hello! | 2023-03-20 08:45:00 |
| 3 | Bob Johnson | bob@example.com | 5 | 3 | New Project | Bob starts a new project. | 2023-05-05 11:00:00 |
SQLの工夫
-
条件絞り込みのみのJOINを行う
- max_postsでは情報取得は行わず、親テーブルに紐づくIDが最大の子テーブルという条件のみを抽出している
- 取得する情報は別途Joinして取得する
実行環境
検証環境は下記にアクセスすれば試せます 