用于结果集中的行自动编号的Pure-SQL技术 [英] Pure-SQL Technique for Auto-Numbering Rows in Result Set

问题描述

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

I'm looking for a way to sequentially number rows in a result set (not a table). In essence, I'm starting with a query like the following:

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

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

The ids are obviously not a true sequence (e.g. 1, 2, 3, 4). What I need is another column in the result set which contains these auto-numberings. I'm willing to use a SQL function if I have to, but I would rather do it without using extensions on the ANSI spec.

平台是MySQL，但该技术应尽可能跨平台使用(因此希望避免非标准扩展).

Platform is MySQL, but the technique should be cross-platform if at all possible (hence the desire to avoid non-standard extensions).

推荐答案

要获得有意义的行号，您需要对结果进行排序.然后您可以执行以下操作:

To have a meaningful row number you need to order your results. Then you can do something like this:

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.

Note that the WHERE clause of the sub query needs to match the WHERE clause or the primary key from the main query and the ORDER BY of the main query.

SQL Server具有ROW_NUMBER()OVER结构来简化此操作，但是我不知道MySQL是否有任何特殊的解决方法.

SQL Server has the ROW_NUMBER() OVER construct to simplify this, but I don't know if MySQL has anything special to address it.

由于我的信息被接受为答案，所以我也想讲出Dan Goldstein的回答，这种回答与方法非常相似，但是使用JOIN而不是子查询，并且通常会表现更好

Since my post here was accepted as the answer, I want to also call out Dan Goldstein's response, which is very similar in approach but uses a JOIN instead of a sub query and will often perform better

这篇关于用于结果集中的行自动编号的Pure-SQL技术的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！