MySQL 5分组取第二高值及获取每组次新时间戳方法问询

Hey there! Let's break down these two MySQL grouping problems for you—since MySQL 5 doesn't support window functions like ROW_NUMBER() which would simplify this, we'll use user-defined variables instead.

假设你的表名为your_table，分组字段是group_column，要查询的数值字段是target_value。我们可以用变量给每个分组内的行按值降序排名，再筛选排名为2的行，就是每组的第二高值：

SELECT group_column, target_value AS second_highest_value
FROM (
    SELECT 
        group_column,
        target_value,
        -- 同分组时排名递增，切换分组时重置为1
        @rank := IF(@current_group = group_column, @rank + 1, 1) AS rank,
        @current_group := group_column
    FROM your_table
    -- 初始化变量
    CROSS JOIN (SELECT @current_group := NULL, @rank := 0) AS vars
    -- 先按分组字段排序，再按目标值降序，确保最大值排第一
    ORDER BY group_column, target_value DESC
) AS ranked_data
WHERE rank = 2;

小提醒： #

• 如果某个分组只有1行数据，结果里不会包含该分组。如果需要显示这类分组并返回NULL作为第二高值，可以用左连接关联原表的分组来补充。
• 要是想找第二小值，把target_value DESC改成target_value ASC就行。

你的原语句是获取每组id的最新时间戳，现在要同时拿到次新的start_time和end_time，用变量排名的方法可以实现：

SELECT 
    id,
    MAX(CASE WHEN rank = 1 THEN start_time END) AS start1,
    MAX(CASE WHEN rank = 1 THEN end_time END) AS end1,
    MAX(CASE WHEN rank = 2 THEN start_time END) AS start2,
    MAX(CASE WHEN rank = 2 THEN end_time END) AS end2
FROM (
    SELECT 
        id,
        start_time,
        end_time,
        -- 按id分组，同id内按start_time降序排名，最新的为rank1
        @rank := IF(@current_id = id, @rank + 1, 1) AS rank,
        @current_id := id
    FROM `table`
    -- 初始化变量
    CROSS JOIN (SELECT @current_id := NULL, @rank := 0) AS vars
    -- 排序逻辑：先按id分组，再按时间戳降序，确保最新的排在前面
    ORDER BY id, start_time DESC
) AS ranked_time
GROUP BY id;

说明： #

• 子查询里给每个id的行按start_time降序排名，最新时间戳对应rank=1，次新对应rank=2。
• 外层用CASE配合MAX函数，把不同排名的时间戳分别提取为start1/end1（最新）和start2/end2（次新）。
• 如果某个id只有1行数据，start2和end2会返回NULL，这是合理的结果。
• 要是需要按end_time判断“最新/次新”，把ORDER BY里的start_time DESC改成end_time DESC即可。

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