MySQL双列排序：固定forcedRank行位置，NULL行填充空位

初始数据表结构及数据 #

+------------+-------------+
| legacyRank | forcedRank  |
+------------+-------------+
| 0          | NULL        |
| 1          | 6           |
| 2          | NULL        |
| 3          | 1           |
| 4          | NULL        |
| 5          | NULL        |
| 6          | 2           |
+------------+-------------+

表生成SQL语句 #

CREATE TABLE two_column_order (
  legacyRank VARCHAR(45),
  forcedRank VARCHAR(45)
);

INSERT INTO two_column_order (legacyRank, forcedRank) VALUES (5, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank) VALUES (6, 2); 
INSERT INTO two_column_order (legacyRank, forcedRank) VALUES (7, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank) VALUES (0, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank) VALUES (1, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank) VALUES (2, 6); 
INSERT INTO two_column_order (legacyRank, forcedRank) VALUES (3, NULL); 
INSERT INTO two_column_order (legacyRank, forcedRank) VALUES (4, 1); 

-- 尝试的排序语句（结果不符合预期）
SELECT * FROM two_column_order
order by
  CASE when `forcedRank` <> NULL THEN `forcedRank`
  ELSE `legacyRank`
  END

需求说明 #

需要实现以下排序规则：

• forcedRank非NULL的行，必须放置在该字段指定的准确位置
• forcedRank为NULL的行，按legacyRank排序后填充到未被固定行占据的空位，且不能移动已固定的行

预期结果：

+------------+-------------+
| legacyRank | forcedRank  |
+------------+-------------+
| 0          | NULL        | -- 位置0：空位列行按legacyRank排序填充
| 3          | 1           | -- 位置1：forcedRank=1的固定行
| 6          | 2           | -- 位置2：forcedRank=2的固定行
| 2          | NULL        | -- 位置3：空位列行填充
| 4          | NULL        | -- 位置4：空位列行填充
| 5          | NULL        | -- 位置5：空位列行填充
| 1          | 6           | -- 位置6：forcedRank=6的固定行
+------------+-------------+

尝试的方案问题 #

之前使用CASE WHEN的排序写法，仅能将固定行按forcedRank排序后放在前面，空位列行按legacyRank跟在后面，完全没实现空位填充的要求，不符合预期的结果如下：

+------------+-------------+
| legacyRank | forcedRank  |
+------------+-------------+
| 3          | 1           |
| 6          | 2           |
| 1          | 6           |
| 0          | NULL        |
| 2          | NULL        |
| 4          | NULL        |
| 5          | NULL        |
+------------+-------------+

解决方案 #

要实现这种“固定位置+空位填充”的排序，需先明确所有目标位置，再分别处理固定行和空位列行，最后将两者合并到对应位置。以下是具体的SQL实现：

WITH all_positions AS (
  -- 生成覆盖所有需要的连续位置序列
  SELECT 0 AS pos
  UNION ALL
  SELECT pos + 1 FROM all_positions
  WHERE pos < (
    SELECT GREATEST(
      MAX(CAST(forcedRank AS UNSIGNED)), 
      MAX(CAST(legacyRank AS UNSIGNED))
    ) FROM two_column_order
  )
),
fixed_rows AS (
  -- 提取固定行，转换forcedRank为数字类型作为匹配位置
  SELECT 
    CAST(forcedRank AS UNSIGNED) AS pos,
    legacyRank,
    forcedRank
  FROM two_column_order
  WHERE forcedRank IS NOT NULL
),
free_rows AS (
  -- 对空位列的行按legacyRank排序，生成行号用于匹配空位
  SELECT 
    legacyRank,
    forcedRank,
    ROW_NUMBER() OVER(ORDER BY CAST(legacyRank AS UNSIGNED)) AS rn
  FROM two_column_order
  WHERE forcedRank IS NULL
),
free_positions AS (
  -- 筛选出未被固定行占用的位置，按顺序生成行号
  SELECT 
    pos,
    ROW_NUMBER() OVER(ORDER BY pos) AS rn
  FROM all_positions
  WHERE pos NOT IN (SELECT pos FROM fixed_rows)
)
-- 合并固定行和填充的空位列行，按位置排序输出
SELECT 
  COALESCE(fixed.legacyRank, free.legacyRank) AS legacyRank,
  COALESCE(fixed.forcedRank, free.forcedRank) AS forcedRank
FROM all_positions ap
LEFT JOIN fixed_rows fixed ON ap.pos = fixed.pos
LEFT JOIN free_positions fp ON ap.pos = fp.pos
LEFT JOIN free_rows free ON fp.rn = free.rn
ORDER BY ap.pos;

逻辑说明 #

1. all_positions：生成从0开始的连续位置序列，范围覆盖所有可能的forcedRank和legacyRank最大值，确保每个目标位置都被包含
2. fixed_rows：将forcedRank非空的行转换为数字位置，方便后续匹配到对应位置
3. free_rows：对forcedRank为空的行按legacyRank排序，给每行分配一个递增的行号
4. free_positions：找出所有未被固定行占用的位置，同样按顺序分配行号
5. 最后通过位置关联，将固定行放到指定位置，空位列的行按行号匹配到空闲位置，最终按位置排序输出即可得到预期结果

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