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

我正在寻找一种顺序编号结果集(而不是表)中的行的方法.从本质上讲,我开始使用如下查询:

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

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

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

要获得有意义的行号,您需要订购结果.然后你可以做这样的事情:

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 BY主查询.

SQL Server有ROW_NUMBER()OVER构造来简化这个,但我不知道MySQL是否有任何特殊的解决方法.

由于我在这里的帖子被接受作为答案,我想也要调用Dan Goldstein的回答,这在方法上非常相似,但是使用JOIN而不是子查询,并且通常会表现得更好

AFAIK,没有"标准"方式.

MS SQL Server有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/中的评论

一个非常低效的想法,但是ANSI SQL将计算具有较小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'