如何用单条SQL语句查询MySQL中每个班级的首个和最后一个事件？

方法一：使用窗口函数（MySQL 8.0+ 推荐） #

利用ROW_NUMBER()窗口函数为每个班级的事件按日期排序并编号，筛选出编号为1的首末事件：

WITH ranked_events AS (
    SELECT 
        classId,
        date,
        message,
        ROW_NUMBER() OVER (PARTITION BY classId ORDER BY date ASC) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY classId ORDER BY date DESC) AS rn_desc
    FROM events
)
SELECT classId, date, message
FROM ranked_events
WHERE rn_asc = 1 OR rn_desc = 1
ORDER BY classId, date;

如果班级仅存在一个事件，该记录会同时满足rn_asc=1和rn_desc=1，因此会返回两次。若希望只返回一次，可在SELECT后添加DISTINCT关键字。

方法二：兼容旧版本MySQL（5.7及以下） #

通过子查询获取每个班级的最早和最晚日期，再关联原表筛选对应事件：

SELECT e.classId, e.date, e.message
FROM events e
INNER JOIN (
    SELECT 
        classId,
        MIN(date) AS first_date,
        MAX(date) AS last_date
    FROM events
    GROUP BY classId
) AS class_dates 
ON e.classId = class_dates.classId 
AND (e.date = class_dates.first_date OR e.date = class_dates.last_date)
ORDER BY e.classId, e.date;

注意：若同一班级在最早/最晚日期存在多条事件，该方法会返回所有对应日期的事件。若需仅返回单条，可结合GROUP BY e.classId, e.date或添加额外排序条件。

可选：首末事件同行展示 #

若希望将每个班级的首末事件信息放在同一行，可使用条件聚合：

SELECT 
    classId,
    MAX(CASE WHEN rn_asc = 1 THEN date END) AS first_date,
    MAX(CASE WHEN rn_asc = 1 THEN message END) AS first_message,
    MAX(CASE WHEN rn_desc = 1 THEN date END) AS last_date,
    MAX(CASE WHEN rn_desc = 1 THEN message END) AS last_message
FROM (
    SELECT 
        classId,
        date,
        message,
        ROW_NUMBER() OVER (PARTITION BY classId ORDER BY date ASC) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY classId ORDER BY date DESC) AS rn_desc
    FROM events
) AS ranked
GROUP BY classId;

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