MySQL按索引操作排序查询结果行：更新/插入需求及SQL问询

首先得指出你原来的SQL里的小问题：你用了COUNT(*)这个聚合函数，它跑完之后只会返回一行结果，后面加的ORDER BY根本不会对排序前的行生效，这肯定不是你想要的效果对吧？

你的核心需求应该是给排序后的每行分配一个专属索引（行号），然后基于这个行号来做条件判断，或者用来更新/插入数据。那窗口函数绝对是你的好帮手，尤其是ROW_NUMBER()，它能轻松给排序后的每行生成连续的序号。

第一步：先给每行生成行号 #

先把question_id=12的所有answer按answer_id升序排好，再给每行加上行号：

SELECT 
  answer_id,
  ROW_NUMBER() OVER (ORDER BY answer_id ASC) AS row_index
FROM Answers
WHERE question_id = 12;

跑出来的结果大概是这样的：

第二步：实现你的条件逻辑（计数到3就停/取对应值） #

看你原来的SQL逻辑，应该是想：如果总共有≥3行，就取第3行的answer_id；如果不足3行，就返回总行数。这里给你两种实现方式：

方式一：用窗口函数嵌套查询 #

SELECT 
  CASE 
    WHEN MAX(row_index) >= 3 THEN (SELECT answer_id FROM (
      SELECT answer_id, ROW_NUMBER() OVER (ORDER BY answer_id ASC) AS row_index
      FROM Answers WHERE question_id = 12
    ) AS ranked WHERE row_index = 3)
    ELSE MAX(row_index)
  END AS result
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY answer_id ASC) AS row_index
  FROM Answers WHERE question_id = 12
) AS counts;

方式二：用OFFSET/FETCH更简洁 #

这种方式直接跳过头2行取第3行，如果没有第3行（总行数<3），就返回总行数：

SELECT COALESCE(
  (SELECT answer_id FROM Answers WHERE question_id = 12 ORDER BY answer_id ASC OFFSET 2 FETCH NEXT 1 ROW ONLY),
  (SELECT COUNT(*) FROM Answers WHERE question_id = 12)
) AS result;

注意这里OFFSET 2是因为它从0开始计数，跳过前2行就是取第3行啦。

第三步：基于行号做更新/插入操作 #

如果你的最终目的是要拿这个行号来更新现有行，或者插入新数据，这里给你两个实用示例：

示例1：把排序后的行号更新到表的某个字段 #

比如你有个index_column字段要存行号，可以这么写：

WITH ranked_answers AS (
  SELECT 
    answer_id,
    ROW_NUMBER() OVER (ORDER BY answer_id ASC) AS row_index
  FROM Answers
  WHERE question_id = 12
)
UPDATE Answers
SET index_column = ranked_answers.row_index
FROM ranked_answers
WHERE Answers.answer_id = ranked_answers.answer_id;

示例2：把带行号的数据插入到其他表 #

INSERT INTO TargetTable (question_id, answer_id, row_index)
SELECT 
  12,
  answer_id,
  ROW_NUMBER() OVER (ORDER BY answer_id ASC) AS row_index
FROM Answers
WHERE question_id = 12;

小补充：旧版本MySQL的兼容方案 #

如果你用的是MySQL 5.x（不支持窗口函数），可以用变量来模拟行号：

SELECT 
  answer_id,
  @row_num := @row_num + 1 AS row_index
FROM Answers, (SELECT @row_num := 0) AS init
WHERE question_id = 12
ORDER BY answer_id ASC;

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