如何为按三个字段排序的表添加连续索引列？

针对百万级数据量的场景，最优的方式是利用窗口函数（现代主流数据库均支持）来生成连续的整数序列，效率远高于用户变量的方式。以下分不同数据库给出具体实现：

1. 支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server 2012+、Oracle等） #

使用ROW_NUMBER()窗口函数，直接在查询中生成从1开始的连续整数。你的完整查询语句可以修改为：

SELECT 
    a.A, 
    a.B, 
    a.C, 
    ROW_NUMBER() OVER(ORDER BY a.C, a.B DESC, a.A) AS D
INTO b 
FROM a 
WHERE a.C IS NOT NULL;

注意事项： #

• 对于MySQL，INTO语法可能不支持，需要改用CREATE TABLE ... SELECT形式：

  CREATE TABLE b AS
  SELECT 
      a.A, 
      a.B, 
      a.C, 
      ROW_NUMBER() OVER(ORDER BY a.C, a.B DESC, a.A) AS D
  FROM a 
  WHERE a.C IS NOT NULL;
  

• 为了提升百万级数据的查询性能，建议给表a创建组合索引：CREATE INDEX idx_a_c_b_a ON a(C, B DESC, A);，这样排序操作可以直接利用索引，避免全表扫描和临时排序。

2. 不支持窗口函数的旧版数据库（如MySQL 5.x） #

如果你的数据库版本较低，不支持窗口函数，可以使用用户变量来实现：

SET @row_num = 0;

SELECT 
    a.A, 
    a.B, 
    a.C, 
    (@row_num := @row_num + 1) AS D
INTO b 
FROM a 
WHERE a.C IS NOT NULL
ORDER BY a.C, a.B DESC, a.A;

注意事项： #

• 这种方式依赖变量的赋值顺序，确保ORDER BY先执行后再赋值变量，否则序列可能不符合预期。
• 百万级数据下，这种方式的效率会低于窗口函数，建议优先升级数据库版本使用窗口函数方案。

效果验证 #

执行上述语句后，表b的D列会按照你指定的排序规则生成连续的整数，和你给出的示例输出完全一致：

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