SQL实现：按ID升序排序后统计每条车辆记录对应更高ID的数量

现有一张存储车辆信息的数据表，包含car（车辆标识）、ID两个字段，初始查询结果已经通过order by ID asc完成ID升序排列。需求为遍历所有排序后的记录，从ID最小值开始，为每一条车辆记录统计ID值严格高于当前记录的总数量。

示例数据 #

• 原始升序查询结果

blue car 1
red car 1
green car 2
yellow car 2

• 预期输出结果

blue car 2
red car 2
green car 0 
yellow car 0

实现方法 #

核心统计逻辑和遍历顺序无关，只需要对每条记录，计算全表范围内ID大于当前记录ID的总记录数即可，相同ID的记录不计入统计。

方法1：窗口函数实现（推荐，适配支持SQL窗口函数的数据库：MySQL8.0+、PostgreSQL、SQL Server等） #

这种写法不需要自连接，执行效率最高：

SELECT
  car,
  ID,
  COUNT(*) OVER (
    ORDER BY ID 
    RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
  ) AS higher_cnt
FROM car_info
ORDER BY ID ASC;

注意：不要将窗口范围写为ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING，ROWS是按结果集的行位置划定范围，遇到同ID的行时会出现统计偏差，RANGE是按排序字段的值划定范围，才能准确匹配所有ID值更大的记录。
代入示例数据运行，ID=1的两行会统计到ID=2的2条记录，ID=2的两行没有更大的ID值，计数为0，和预期结果完全一致。

方法2：自连接实现（适配不支持窗口函数的老版本数据库，如MySQL5.x） #

通过左连接关联出所有ID比当前行大的记录，再分组计数即可：

SELECT
  t1.car,
  t1.ID,
  COUNT(t2.ID) AS higher_cnt
FROM car_info t1
LEFT JOIN car_info t2 ON t2.ID > t1.ID
GROUP BY t1.car, t1.ID
ORDER BY t1.ID ASC;

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