MariaDB(MySQL)でテーブルから更新日が新しいレコードのみ取得

テスト用データベースとテーブルの作成

説明のためにテスト用データベースとテーブル構造の作成です。

-- データベース作成
CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE db_test;
-- テーブル作成
CREATE TABLE tm_user(user_id SERIAL, user_name VARCHAR(20));
CREATE TABLE t_user_favorite(favorite_id SERIAL, user_id INT, favorite VARCHAR(100), up_date DATETIME DEFAULT NOW() ON UPDATE NOW());

-- テーブル構造の確認
DESC tm_user;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| user_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(20)         | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
DESC t_user_favorite;
+-------------+---------------------+------+-----+---------------------+-------------------------------+
| Field       | Type                | Null | Key | Default             | Extra                         |
+-------------+---------------------+------+-----+---------------------+-------------------------------+
| favorite_id | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment                |
| user_id     | int(11)             | YES  |     | NULL                |                               |
| favorite    | varchar(100)        | YES  |     | NULL                |                               |
| up_date     | datetime            | YES  |     | current_timestamp() | on update current_timestamp() |
+-------------+---------------------+------+-----+---------------------+-------------------------------+

レコードの挿入

テスト用テーブルにレコードを挿入します。

INSERT INTO tm_user(user_name) values('森'),('佐藤'),('仮屋崎');
SELECT * FROM tm_user;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
|       1 | 森        |
|       2 | 佐藤      |
|       3 | 仮屋崎    |
+---------+-----------+

INSERT INTO t_user_favorite(user_id, favorite, up_date) values(1, '納豆', '2024-01-01');
INSERT INTO t_user_favorite(user_id, favorite, up_date) values(1, '餃子', '2024-02-01');
INSERT INTO t_user_favorite(user_id, favorite, up_date) values(3, 'りんご', '2024-01-01');
INSERT INTO t_user_favorite(user_id, favorite, up_date) values(3, 'ぶどう', '2024-02-01');
INSERT INTO t_user_favorite(user_id, favorite, up_date) values(3, 'みかん', '2024-03-01');
SELECT * FROM t_user_favorite;
+-------------+---------+-----------+---------------------+
| favorite_id | user_id | favorite  | up_date             |
+-------------+---------+-----------+---------------------+
|           1 |       1 | 納豆      | 2024-01-01 00:00:00 |
|           2 |       1 | 餃子      | 2024-02-01 00:00:00 |
|           3 |       3 | りんご    | 2024-01-01 00:00:00 |
|           4 |       3 | ぶどう    | 2024-02-01 00:00:00 |
|           5 |       3 | みかん    | 2024-03-01 00:00:00 |
+-------------+---------+-----------+---------------------+

SQLの作成

１．ユーザー毎の更新日の一覧を取得

「好みの履歴(t_user_favorite)」テーブルからユーザー毎の更新日の一覧を取得するSQLは簡単で以下の通りです。

SELECT user_id, MAX(up_date) max_date FROM t_user_favorite GROUP BY user_id;
+---------+---------------------+
| user_id | max_date            |
+---------+---------------------+
|       1 | 2024-02-01 00:00:00 |
|       3 | 2024-03-01 00:00:00 |
+---------+---------------------+

２．「好みの履歴」テーブルと上記１．をジョインする

「好みの履歴(t_user_favorite)」テーブルと上記１．をジョイン（INNER JOIN)します。

SELECT t_user_favorite.user_id, t_user_favorite.favorite
FROM t_user_favorite
INNER JOIN 
  (SELECT user_id, MAX(up_date) max_date FROM t_user_favorite GROUP BY user_id) t_ufmax
ON t_ufmax.user_id = t_user_favorite.user_id AND t_ufmax.max_date = t_user_favorite.up_date;
+---------+-----------+
| user_id | favorite  |
+---------+-----------+
|       1 | 餃子      |
|       3 | みかん    |
+---------+-----------+

３．「ユーザー」テーブルと上記２．をジョインする

「ユーザー(tm_user)」テーブルと上記２．をジョイン（LEFT JOIN)します。

SELECT tm_user.user_id,tm_user.user_name, IFNULL(t_uf.favorite,'') favorite
FROM tm_user
LEFT JOIN
  (
    SELECT t_user_favorite.user_id, t_user_favorite.favorite
    FROM t_user_favorite
    INNER JOIN 
      (SELECT user_id, MAX(up_date) max_date FROM t_user_favorite GROUP BY user_id) t_ufmax
    ON t_ufmax.user_id = t_user_favorite.user_id AND t_ufmax.max_date = t_user_favorite.up_date
  ) t_uf
ON t_uf.user_id = tm_user.user_id;
+---------+-----------+-----------+
| user_id | user_name | favorite  |
+---------+-----------+-----------+
|       1 | 森        | 餃子      |
|       2 | 佐藤      |           |
|       3 | 仮屋崎    | みかん    |
+---------+-----------+-----------+

ER図は以下のようになります。