SQL如何按条件查询每个父记录对应的最新子记录ID

问题原因 #

你原有自连接的逻辑是匹配全局范围内同一Quiz下ID比当前attempts1更大的尝试记录，只要存在这类记录就会过滤掉当前attempts1。当你仅给attempts1添加日期筛选条件时，以QuizID为3的记录为例，1月1日范围内的最新记录是ID=4，但全局存在ID=5、6两条更大的记录，所以连接后attempts2.ID不为空，会被过滤，最终导致查询结果为空。

解决方案 #

方案1：修改自连接逻辑

给右表attempts2也加上相同的日期筛选，让比较逻辑限定在同一日期范围内即可：

SELECT 
    attempts1.QuizID, 
    attempts1.ID AS QuizAttemptID, 
    attempts1.AttemptedAt
FROM QuizAttempts AS attempts1
LEFT OUTER JOIN QuizAttempts AS attempts2
ON (
    attempts1.QuizID = attempts2.QuizID
    AND attempts1.ID < attempts2.ID
    -- 右表增加相同日期筛选，仅比较同范围内的记录
    AND attempts2.AttemptedAt BETWEEN '2021-01-01 00:00:00' AND '2021-01-01 23:59:59'
)
WHERE 
    attempts1.AttemptedAt BETWEEN '2021-01-01 00:00:00' AND '2021-01-01 23:59:59'
    AND attempts2.ID IS NULL;

方案2：使用窗口函数（更简洁，主流数据库均支持）

用ROW_NUMBER窗口函数直接对符合条件的记录按Quiz分组排序，取每组第一条即可，逻辑更清晰：

SELECT QuizID, ID AS QuizAttemptID, AttemptedAt
FROM (
    SELECT 
        QuizID, ID, AttemptedAt,
        ROW_NUMBER() OVER (PARTITION BY QuizID ORDER BY AttemptedAt DESC, ID DESC) AS rn
    FROM QuizAttempts
    WHERE AttemptedAt BETWEEN '2021-01-01 00:00:00' AND '2021-01-01 23:59:59'
) t
WHERE rn = 1;

注：你给出的期望输出里AttemptedAt的年份写为2020属于笔误，实际执行后返回的日期为2021年对应值。

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