向 SQL 查询的结果集添加行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20232641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Add a row number to result set of a SQL query
提问by Trojan.ZBOT
I have a simple select statement. I want to add a temporary column which will number the rows in my result set. I tried this -
我有一个简单的选择语句。我想添加一个临时列，它将为我的结果集中的行编号。我试过这个 -
declare @num int
set @num = 0;
select t.A, t.B, t.C, (@count + 1) as number
from tableZ as t
It assigns the 1 to all rows. I tried @count = @count + 1 and it did not work. How do I do this thing in a simple manner ?
它将 1 分配给所有行。我试过@count = @count + 1 但它没有用。我如何以简单的方式做这件事？
thanks.
谢谢。
回答by Shai
SELECT
t.A,
t.B,
t.C,
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS number
FROM tableZ AS t
See working example at SQLFiddle
请参阅SQLFiddle 的工作示例
Of course, you may want to define the row-numbering order – if so, just swap OVER (ORDER BY (SELECT 1))for, e.g., OVER (ORDER BY t.C), like in a normal ORDER BYclause.
当然，您可能想要定义行编号顺序——如果是这样，只需交换OVER (ORDER BY (SELECT 1))，例如OVER (ORDER BY t.C)，就像在普通ORDER BY子句中一样。
回答by Aaron Bertrand
The typical pattern would be as follows, but you need to actually define how the ordering should be applied (since a table is, by definition, an unordered bag of rows):
典型的模式如下，但您需要实际定义应如何应用排序（因为根据定义，表是一个无序的行袋）：
SELECT t.A, t.B, t.C, number = ROW_NUMBER() OVER (ORDER BY t.A)
FROM dbo.tableZ AS t
ORDER BY t.A;
Not sure what the variables in your question are supposed to represent (they don't match).
不确定您问题中的变量应该代表什么（它们不匹配）。
回答by Miles Gillham
So before MySQL 8.0 there is no ROW_NUMBER() function. Accpted answer rewritten to support older versions of MySQL:
所以在 MySQL 8.0 之前没有 ROW_NUMBER() 函数。Accpted 答案重写以支持旧版本的 MySQL：
SET @row_number = 0;
SELECT t.A, t.B, t.C, (@row_number:=@row_number + 1) AS number
FROM dbo.tableZ AS t ORDER BY t.A;
