如何按姓名保留对应最大归还年份的任意一组重复书籍记录？

表结构与测试数据 #

CREATE TABLE books_returned 
(
    name VARCHAR(50),
    book_id VARCHAR(50),
    year_book_returned INT
);

INSERT INTO books_returned (name, book_id, year_book_returned) 
VALUES
('john', 'julius ceasar', 2010),
('john', 'julius caesar', 2010),
('john', 'hamlet', 2010),
('john', 'hamlet', 2010),
('john', 'othello', 2009),
('john', 'othello', 2009),
('kevin', 'macbeth', 2015),
('kevin', 'tempest', 2020),
('david', 'romeojuliet', 2010),
('david', 'romeojuliet', 2010),
('david', 'romeojuliet', 2010),
('david', 'king lear', 2005);

需求说明 #

对每个name，保留对应最大year_book_returned的任意一组book_id的所有行记录（存在并列时任选其一即可）。

原查询的问题 #

原查询仅筛选出每个用户最大归还年份的所有记录，无法实现“只保留任意一组book_id”的要求。例如对于用户john，原查询会返回2010年归还的所有书籍（julius ceasar、julius caesar、hamlet）的全部行，不符合需求。

原查询代码：

SELECT br.*
FROM books_returned br
JOIN (
    SELECT name, MAX(year_book_returned) as max_year
    FROM books_returned
    GROUP BY name
) as subquery
ON br.name = subquery.name AND br.year_book_returned = subquery.max_year;

正确查询方案（使用窗口函数） #

以下是基于ROW_NUMBER()窗口函数结合随机排序的实现，能随机选中每个用户最大归还年份下的一组book_id，并返回该组的所有行：

WITH user_max_year AS (
    -- 计算每个用户的最大归还年份
    SELECT name, MAX(year_book_returned) AS max_year
    FROM books_returned
    GROUP BY name
),
selected_book_groups AS (
    -- 在每个用户的最大年份下，筛选出所有唯一的book_id并随机排序，标记选中的组
    SELECT 
        br.name,
        br.book_id,
        ROW_NUMBER() OVER (PARTITION BY br.name ORDER BY random()) AS group_rank
    FROM books_returned br
    JOIN user_max_year umy 
        ON br.name = umy.name 
        AND br.year_book_returned = umy.max_year
    GROUP BY br.name, br.book_id  -- 去重得到每个用户最大年份下的唯一书籍组
)
-- 关联原表，取出选中的书籍组的所有行
SELECT br.*
FROM books_returned br
JOIN selected_book_groups sbg 
    ON br.name = sbg.name 
    AND br.book_id = sbg.book_id
WHERE sbg.group_rank = 1;

逻辑说明 #

1. user_max_year CTE：先计算每个用户的最大归还年份，作为后续筛选的基础。
2. selected_book_groups CTE：关联原表和最大年份数据，先通过GROUP BY得到每个用户最大年份下的所有唯一book_id；再用ROW_NUMBER()按random()排序，给每个用户的书籍组分配编号，编号为1的即为随机选中的一组。
3. 最后关联原表，取出选中的书籍组的所有行记录，满足需求。

如果希望固定选择某组而非随机，只需将ORDER BY random()替换为其他排序规则（例如ORDER BY book_id）即可。

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