如何用SQL获取每个学生每月最新的健康检查记录

针对你遇到的问题，这里提供两种常用的SQL实现方式，确保每个学生每月只保留最新的检查条目：

方案一：使用ROW_NUMBER()窗口函数（通用兼容多数数据库） #

窗口函数可以给每个学生的月度记录按时间排序，然后筛选出排名第一的最新记录：

WITH ranked_inspections AS (
    SELECT 
        mts.student_id,
        mti.category,
        mti.created_date AS entry_date,
        -- 按学生分组，每月内按创建时间降序排名
        ROW_NUMBER() OVER (
            PARTITION BY mts.student_id, DATE_TRUNC('month', mti.created_date)
            ORDER BY mti.created_date DESC
        ) AS rn
    FROM public.mt_tagged_student mts
    JOIN public.mt_student ms ON mts.student_id = ms.id
    JOIN public.mt_inspection mti ON mti.student_id = ms.id
    WHERE 
        mti.created_date BETWEEN '2023-10-01 00:00:01' AND '2023-10-31 23:59:59'
        AND mti.category = 1
)
SELECT student_id, category, entry_date
FROM ranked_inspections
WHERE rn = 1
ORDER BY student_id ASC;

关键说明： #

• PARTITION BY mts.student_id, DATE_TRUNC('month', mti.created_date)：按学生ID和记录所属月份分组，确保每个学生每个月的记录单独排序
• ORDER BY mti.created_date DESC：每组内按创建时间倒序，最新的记录排名为1
• 最后筛选rn = 1即可得到每个学生每月的最新条目

方案二：使用PostgreSQL的DISTINCT ON（更简洁） #

如果你的数据库是PostgreSQL，可以利用DISTINCT ON特性直接实现：

SELECT DISTINCT ON (mts.student_id, DATE_TRUNC('month', mti.created_date))
    mts.student_id,
    mti.category,
    mti.created_date AS entry_date
FROM public.mt_tagged_student mts
JOIN public.mt_student ms ON mts.student_id = ms.id
JOIN public.mt_inspection mti ON mti.student_id = ms.id
WHERE 
    mti.created_date BETWEEN '2023-10-01 00:00:01' AND '2023-10-31 23:59:59'
    AND mti.category = 1
ORDER BY 
    mts.student_id, 
    DATE_TRUNC('month', mti.created_date), 
    mti.created_date DESC;

关键说明： #

• DISTINCT ON (列1, 列2)：会保留每组（列1+列2）的第一条记录
• 排序时必须先按DISTINCT ON指定的列排序，再按created_date DESC确保最新的记录排在每组的第一位

补充说明： #

你原SQL中的LEFT JOIN实际会被WHERE条件转换为INNER JOIN（因为过滤了mt_inspection的字段），如果需要保留没有当月检查记录的学生，需要将WHERE中的mt_inspection条件移到JOIN子句中，同时调整窗口函数的逻辑避免过滤掉这些学生。

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