结果集中行自动编号的纯SQL技术

Question

我正在寻找一种方法来对结果集(而不是表)中的行进行顺序编号。本质上，我是从如下的查询开始的：

SELECT id, name FROM people WHERE name = 'Spiewak'

ids显然不是真正的序列(例如1, 2, 3, 4)。我需要的是结果集中的另一列，它包含这些自动编号。如果有必要，我愿意使用SQL函数，但我宁愿不使用ANSI规范上的扩展。

平台是MySQL，但是如果可能的话，这种技术应该是跨平台的(因此希望避免非标准扩展)。

Answer 1

要有一个有意义的行号，您需要对结果进行排序。然后你可以做这样的事情：

SELECT id, name
    , (SELECT COUNT(*) FROM people p2 WHERE name='Spiewak' AND p2.id <= p1.id) AS RowNumber
FROM people p1
WHERE name = 'Spiewak'
ORDER BY id

注意，子查询的WHERE子句需要匹配主查询的WHERE子句或主键以及主查询的ORDER。

Server有ROW_NUMBER() OVER结构来简化这一点，但我不知道MySQL是否有什么特别的地方来解决它。

由于我在这里的帖子被接受为答案，我还想调用Dan Goldstein的响应，这个响应在方法上非常相似，但使用的是连接而不是子查询，并且通常会执行得更好。

Answer 2

AFAIK，没有“标准”方式。

MySQL有row_number()，而MySQL没有。

在MySQL中这样做的最简单方法是

SELECT a.*, @num := @num + 1 b from test a, (SELECT @num := 0) d;

资料来源：http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/中的评论

Answer 3

一个方法效率很低，但是ANSI将是计算与相同标准匹配的id较少的行数。我还没有测试过这个SQL，而且它可能无法工作，但如下所示：

SELECT id, name, sub.lcount
FROM people outer
JOIN (SELECT id, COUNT(id) lcount FROM people WHERE name = 'Spiewak' AND id < outer.id GROUP BY id) sub on outer.id = sub.id
WHERE name = 'Spiewak'