从两张表获取含最大日期条件的数据及SQL去重求助

需求与表结构 #

需求 #

获取Subject.name = 'Subject 1'对应的Days记录中，每个name分组下dateTo最大的那条数据。

表结构 #

• Subject表：

{id = 1, name = 'Subject 1', status = true},
{id = 2, name = 'Subject 2', status = true},
{id = 3, name = 'Subject 3', status = true}

• Days表：

{id = 1, idSubject = 1, name = 'test1', dateFrom = '2022-10-01', dateTo = '2022-10-10'},
{id = 2, idSubject = 1, name = 'test1', dateFrom = '2022-10-02', dateTo = '2022-12-01'},
{id = 3, idSubject = 1, name = 'test2', dateFrom = '2022-01-01', dateTo = '2022-05-10'},
{id = 4, idSubject = 1, name = 'test90', dateFrom = '2022-01-05', dateTo = '2022-12-06'},
{id = 5, idSubject = 2, name = 'ex1', dateFrom = '2022-10-01', dateTo = '2025-01-05'}

期望结果 #

{id = 2, idSubject = 1, name = 'test1', dateFrom = '2022-10-02', dateTo = '2022-12-01'},
{id = 4, idSubject = 1, name = 'test90', dateFrom = '2022-01-05', dateTo = '2022-12-06'}

原SQL问题分析 #

你写的SQL子查询中，group by id, date_from, date_to完全失去了分组意义——因为id是Days表的唯一主键，每一行的id都不同，所以这个分组会把每一条Days记录单独作为一组，max(date_to)和max(date_from)就是该行自身的日期值。最终子查询返回所有Days记录，和Subject表join后自然会返回所有关联的记录，而非你期望的筛选后结果。

解决方案 #

这里提供两种常用的解决方法：

方法1：使用窗口函数（推荐） #

利用ROW_NUMBER()窗口函数，按idSubject和name分组，对dateTo降序排序，取每组的第一条记录：

SELECT s.id AS subject_id, d.id, d.idSubject, s.name AS subject_name, d.name, d.dateFrom, d.dateTo
FROM Subject s
JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY idSubject, name ORDER BY dateTo DESC) AS rn
    FROM Days
) d ON s.id = d.idSubject
WHERE s.name = 'Subject 1' AND d.rn = 1
ORDER BY d.dateTo DESC;

方法2：先分组取最大日期再关联 #

先查询每个idSubject+name组合对应的最大dateTo，再关联回Days表获取完整记录：

SELECT s.id AS subject_id, d.id, d.idSubject, s.name AS subject_name, d.name, d.dateFrom, d.dateTo
FROM Subject s
JOIN Days d ON s.id = d.idSubject
JOIN (
    SELECT idSubject, name, MAX(dateTo) AS max_dateTo
    FROM Days
    GROUP BY idSubject, name
) max_d ON d.idSubject = max_d.idSubject 
        AND d.name = max_d.name 
        AND d.dateTo = max_d.max_dateTo
WHERE s.name = 'Subject 1'
ORDER BY d.dateTo DESC;

这两种方法都能准确筛选出每个name分组下dateTo最大的记录，避免重复。

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