如何用MySQL查询获取type=3的最新记录及其他类型数据

需求说明：从galleries表中，获取每个user_id下type=3的最新一条记录，同时保留所有type≠3的记录。

表结构及测试数据 #

CREATE TABLE galleries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    type INT,
    photo VARCHAR(255)
);

INSERT INTO galleries VALUES
(1, 1, 1, 'img1.jpg'),
(2, 2, 1, 'img2.jpg'),
(3, 1, 3, 'img3.jpg'),
(4, 1, 2, 'img4.jpg'),
(5, 1, 3, 'img5.jpg'),
(6, 3, 1, 'img6.jpg'),
(7, 2, 3, 'img7.jpg');

期望输出 #

+----+----------+-------+----------+
| id | user_id  | type  | photo    |
+----+----------+-------+----------+
|  1 | 1        | 1     | img1.jpg |
|  2 | 2        | 1     | img2.jpg |
|  4 | 1        | 2     | img4.jpg |
|  5 | 1        | 3     | img5.jpg |
|  6 | 3        | 1     | img6.jpg |
|  7 | 2        | 3     | img7.jpg |
+----+----------+-------+----------+

解决方案 #

方案一：使用窗口函数（MySQL 8.0+ 适用） #

利用ROW_NUMBER()窗口函数对每个user_id下的type=3记录按id降序编号，仅保留编号为1的最新记录，同时保留所有type≠3的记录：

SELECT id, user_id, type, photo
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY user_id, type ORDER BY id DESC) AS rn
    FROM galleries
) t
WHERE type != 3 OR rn = 1;

方案二：子查询+UNION ALL（兼容MySQL 5.x） #

分两部分查询后合并结果：第一部分直接获取type≠3的所有记录；第二部分通过子查询找到每个user_id下type=3的最大id（即最新记录），再关联原表获取完整数据：

-- 获取所有type≠3的记录
SELECT id, user_id, type, photo
FROM galleries
WHERE type != 3

UNION ALL

-- 获取每个user_id下type=3的最新记录
SELECT g.id, g.user_id, g.type, g.photo
FROM galleries g
INNER JOIN (
    SELECT user_id, MAX(id) AS max_id
    FROM galleries
    WHERE type = 3
    GROUP BY user_id
) t ON g.user_id = t.user_id AND g.id = t.max_id;

内容的提问来源于stack exchange，提问作者Sahu CK