如何使用MySQL GROUP BY查询获取分组中第二个条目的ID？

要实现这个需求，用窗口函数是最简洁高效的方案，尤其是ROW_NUMBER()可以帮我们给每个分组内的条目排序并标记行号，结合分组计数就能精准筛选出目标ID。

具体实现思路 #

1. 给每个data_capture_id（也就是你说的temp_id）分组内的记录分配行号，排序规则根据你对“第二个条目”的定义来定——比如按request_id排序，或者按自增的id排序（如果id是插入顺序的话）。
2. 同时计算每个分组的总条目数，用来筛选出重复项（item_count > 1）。
3. 最后筛选出分组条目数大于1且行号为2的记录，提取对应的id即可。

示例SQL代码 #

支持CTE的数据库（如MySQL 8+、PostgreSQL、SQL Server等）

SET @old_set_id = 71, @new_set_id = 72;

WITH ranked_items AS (
    SELECT 
        id,
        request_id,
        data_capture_id AS temp_id,
        -- 这里用request_id排序，如果你要按插入顺序，改成ORDER BY id即可
        ROW_NUMBER() OVER (PARTITION BY data_capture_id ORDER BY request_id) AS row_num,
        COUNT(data_capture_id) OVER (PARTITION BY data_capture_id) AS item_count
    FROM my_table
    WHERE request_id IN (@old_set_id, @new_set_id)
)
SELECT id
FROM ranked_items
WHERE item_count > 1 AND row_num = 2;

不支持CTE的老版本数据库（如MySQL 5.x）

用子查询替代CTE即可：

SET @old_set_id = 71, @new_set_id = 72;

SELECT id
FROM (
    SELECT 
        id,
        request_id,
        data_capture_id AS temp_id,
        ROW_NUMBER() OVER (PARTITION BY data_capture_id ORDER BY request_id) AS row_num,
        COUNT(data_capture_id) OVER (PARTITION BY data_capture_id) AS item_count
    FROM my_table
    WHERE request_id IN (@old_set_id, @new_set_id)
) AS subquery
WHERE item_count > 1 AND row_num = 2;

关键细节说明 #

• PARTITION BY data_capture_id：表示按temp_id分组处理每条记录。
• ORDER BY request_id：决定了分组内条目的排序顺序，直接影响“第二个条目”的判定——如果你的“第二个”是指后插入的那条，换成ORDER BY id DESC再取row_num=1也可以，根据实际需求调整。
• 窗口函数COUNT(...) OVER (...)能高效计算每个分组的总条目数，不用单独做一次分组查询再关联，性能更优。

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