始终递增的 mysql 行号实现(从 1 到 n 排序)

Question

我以前的搜索结果(包含真正帮助我的 MySQL - row number in recordset?)并不令人满意，因为按其他列排序会覆盖 @RowNumber 的原生排序。

考虑下面我简化的 member 表:

row_number | id | firstname | lastname
--------------------------------------
1          | 1  | Steve     | Jobs
2          | 2  | Bill      | Gates
3          | 3  | Rasmus    | Lerdorf
4          | 4  | Linus     | Torvalds

由以下查询产生:

SELECT id, firstname, lastname, @RowNumber = @RowNumber + 1 AS row_number
FROM member, (SELECT @RowNumber := 0) AS counter_table

但是当我要根据 row_number 或 id 以外的某些列进行排序时，row_number 的排序将会损坏:

SELECT id, firstname, lastname, @RowNumber = @RowNumber + 1 AS row_number
FROM member, (SELECT @RowNumber := 0) AS counter_table
ORDER BY firstname DESC

结果:

row_number | id | firstname | lastname
--------------------------------------
2          | 2  | Bill      | Gates
4          | 4  | Linus     | Torvalds
3          | 3  | Rasmus    | Lerdorf
1          | 1  | Steve     | Jobs

注意:表、查询和任何其他东西都不是经过测试的示例。只有问题本身在现实世界中!

我认为那些简单的情况很容易理解和解决。 (如果它们有任何问题，请忘记那些小错误并考虑重要问题本身)

Answer 1

这应该很明显:先对行编号，然后再排序。

SELECT * FROM (
  SELECT id, firstname, lastname, @RowNumber = @RowNumber + 1 AS row_number
  FROM member, (SELECT @RowNumber := 0) AS counter_table
) AS sq
ORDER BY firstname DESC

哦等等......你实际上想要相反的方式，先订购，后编号:

SELECT sq.*, @RowNumber = @RowNumber + 1 AS row_number FROM (
  SELECT id, firstname, lastname
  FROM member
  ORDER BY firstname DESC
) AS sq
CROSS JOIN (SELECT @RowNumber := 0) AS counter_table