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

要获得有意义的行号,您需要对结果进行排序。然后你可以这样做:

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子句或主键匹配 和 主查询的排序依据。

SQL Server在构造上有行数()来简化这一点,但我不知道MySQL是否有什么特殊的解决方法。

因为我在这里的帖子被接受为答案,所以我还想引用Dan Goldstein的回答,这在方法上非常相似,但使用联接而不是子查询,通常会表现得更好

哎呀,没有“标准”的方法。

在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'

据我所知,没有一种ANSI标准方法可以做到这一点。

在SQL Server中有一个可以使用的ROW_NUMBER()函数,在Oracle中有一个ROWNUM伪列。

在MySQL中,有 this technique

SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo

在oracle中,我所知道的唯一一个数据库就是对数据进行子选择

即

select rownum, id , blah, blah
from  (
select id, name FROM people WHERE name = 'Spiewak'
)

基本概念是rownum将根据从内部select返回的结果集进行计算。

此页面应为您提供一种标准的SQL方法:

https://www.sqlteam.com/articles/returning-a-row-number-in-a-query

希望这有帮助。

我知道这是一条老线索,但我刚才在寻找这个答案。我在MySQL中尝试了Dan Goldstein的查询,但由于“outer”是一个保留字,所以它没有按编写的那样工作。然后,我注意到它仍然在使用子查询。

  SELECT SUM(IF(p1.id > p2.id, 0, 1)) AS `row`, p2.id, p2.name
  FROM people p1 JOIN people p2 ON p1.name = p2.name
  WHERE p1.name = 'Spiewak'
  GROUP BY p2.id

这在MySQL 5.1中对我很有效。对于MySQL,按p2.id分组似乎就足够了。可以将p2.id的显式ORDER添加到查询的末尾,但无论采用哪种方式,我都得到了相同的结果。

这个问题的最新答案,但我相信窗口函数现在是ANSI SQL的标准,或者至少是最新的SQL风格。因此,请使用ROW_NUMBER()函数。

SELECT
    p.ROW_NUMBER() over (order by id) as 'row_id',
    p.id as 'id',
    p.name as 'name'
FROM
    people p
WHERE
    p.name = 'Spiewak'