如何获取每个行李的第二新位置？SQL查询问题求助

你的原语句有两个问题：一是语法错误（select max d.time缺少括号，应该写成select max(d.time)），更关键的是逻辑错误——你用了全局的最大时间来过滤，而不是每个行李自己的最新时间，这会导致部分行李的第二新位置无法被正确匹配。

下面给你几种可行的解决方案，按推荐程度排序：

1. 使用窗口函数（推荐，简洁高效） #

现在绝大多数主流数据库（MySQL 8+、PostgreSQL、SQL Server等）都支持窗口函数，这是最直观且性能最优的方式：

SELECT location, time, bag_id
FROM (
    SELECT 
        *,
        -- 按行李ID分组，每组内按时间倒序编号，最新的为1，第二新的为2
        ROW_NUMBER() OVER (PARTITION BY bag_id ORDER BY time DESC) AS row_num
    FROM bag_position
) ranked_bags
WHERE row_num = 2;

说明： #

• PARTITION BY bag_id：将数据按行李ID分组，每个组独立处理
• ORDER BY time DESC：每个组内按时间从新到旧排序
• ROW_NUMBER()：给每组内的行分配序号，最新的位置序号为1，第二新的为2，最后筛选序号为2的行即可。

2. 修正你原有的子查询思路 #

如果你坚持用子查询的方式，只需要把全局最大时间改成每个行李自己的最新时间即可，修正后的语句如下：

SELECT b.*
FROM bag_position b
WHERE time = (
    SELECT MAX(c.time)
    FROM bag_position c
    WHERE c.bag_id = b.bag_id
      -- 这里要过滤掉当前行李的最新时间，而不是全局的
      AND c.time < (
          SELECT MAX(d.time)
          FROM bag_position d
          WHERE d.bag_id = b.bag_id
      )
);

说明： #

内层的子查询SELECT MAX(d.time) FROM bag_position d WHERE d.bag_id = b.bag_id会先获取当前行李的最新时间，然后在外层子查询中找到该行李所有早于这个时间的记录里的最大时间，也就是第二新的位置。

3. 适用于老版本数据库的计数法 #

如果你的数据库不支持窗口函数（比如MySQL 5.x及以前），可以用计数的方式实现：

SELECT b.*
FROM bag_position b
WHERE (
    -- 统计同一个行李ID下，时间大于等于当前行时间的记录数
    SELECT COUNT(*)
    FROM bag_position c
    WHERE c.bag_id = b.bag_id AND c.time >= b.time
) = 2;

说明： #

对于每一行记录，统计同一个行李ID下时间不晚于它的记录数量：

• 最新的位置，统计结果是1（只有它自己时间最大）
• 第二新的位置，统计结果是2（它和最新的位置时间都大于等于它）
• 更早的位置统计结果会大于2，所以筛选等于2的行即可。

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