带有行计数器的 SQLServer SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/492679/
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
SQLServer SQL query with a row counter
提问by andynormancx
I have a SQL query, that returns a set of rows:
我有一个 SQL 查询，它返回一组行：
SELECT id, name FROM users where group = 2
I need to also include a column that has an incrementing integer value, so the first row needs to have a 1 in the counter column, the second a 2, the third a 3 etc
我还需要包含一个具有递增整数值的列，所以第一行需要在计数器列中有一个 1，第二行需要有一个 2，第三行需要有一个 3 等
The query shown here is just a simplified example, in reality the query could be arbitrarily complex, with several joins and nested queries.
此处显示的查询只是一个简化示例，实际上该查询可以是任意复杂的，具有多个连接和嵌套查询。
I know this could be achieved using a temporary table with an autonumber field, but is there a way of doing it within the query itself ?
我知道这可以使用带有自动编号字段的临时表来实现，但是有没有办法在查询本身中做到这一点？
回答by LukeH
For starters, something along the lines of:
对于初学者来说，大致如下：
SELECT my_first_column, my_second_column,
ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
FROM my_table
However, it's important to note that the ROW_NUMBER() OVER (ORDER BY ...)construct only determines the values of Row_Counter, it doesn't guarantee the ordering of the results.
但是，重要的是要注意，该ROW_NUMBER() OVER (ORDER BY ...)构造仅确定 的值Row_Counter，并不能保证结果的排序。
Unless the SELECTitself has an explicit ORDER BYclause, the results could be returned in any order, dependent on how SQL Server decides to optimise the query. (See this article for more info.)
除非它SELECT本身有一个明确的ORDER BY子句，否则结果可以以任何顺序返回，这取决于 SQL Server 决定如何优化查询。（有关更多信息，请参阅此文章。）
The only way to guarantee that the results will alwaysbe returned in Row_Counterorder is to apply exactly the same ordering to both the SELECTand the ROW_NUMBER():
保证结果总是按Row_Counter顺序返回的唯一方法是对 theSELECT和 the应用完全相同的排序ROW_NUMBER()：
SELECT my_first_column, my_second_column,
ROW_NUMBER() OVER (ORDER BY my_order_column) AS Row_Counter
FROM my_table
ORDER BY my_order_column -- exact copy of the ordering used for Row_Counter
The above pattern will always return results in the correct order and works well for simple queries, but what about an "arbitrarily complex" query with perhaps dozens of expressions in the ORDER BYclause? In those situations I prefer something like this instead:
上面的模式将始终以正确的顺序返回结果，并且适用于简单的查询，但是“任意复杂”的查询在ORDER BY子句中可能有几十个表达式呢？在这些情况下，我更喜欢这样的事情：
SELECT t.*
FROM
(
SELECT my_first_column, my_second_column,
ROW_NUMBER() OVER (ORDER BY ...) AS Row_Counter -- complex ordering
FROM my_table
) AS t
ORDER BY t.Row_Counter
Using a nested query means that there's no need to duplicate the complicated ORDER BYclause, which means less clutter and easier maintenance. The outer ORDER BY t.Row_Counteralso makes the intent of the query much clearer to your fellow developers.
使用嵌套查询意味着不需要复制复杂的ORDER BY子句，这意味着更少的混乱和更容易的维护。外部ORDER BY t.Row_Counter还使您的开发人员同事更清楚查询的意图。
回答by Cade Roux
In SQL Server 2005 and up, you can use the ROW_NUMBER()function, which has options for the sort order and the groups over which the counts are done (and reset).
在 SQL Server 2005 及更高版本中，您可以使用该ROW_NUMBER()函数，该函数具有用于排序顺序和完成计数（和重置）的组的选项。
回答by Clark Vera
The simplest way is to use a variable row counter. However it would be two actual SQL commands. One to set the variable, and then the query as follows:
最简单的方法是使用可变行计数器。但是，这将是两个实际的 SQL 命令。一是设置变量，然后查询如下：
SET @n=0;
SELECT @n:=@n+1, a.* FROM tablename a
Your query can be as complex as you like with joins etc. I usually make this a stored procedure. You can have all kinds of fun with the variable, even use it to calculate against field values. The key is the :=
您的查询可以与您喜欢的连接等一样复杂。我通常将其设为存储过程。您可以使用变量获得各种乐趣，甚至可以使用它来计算字段值。关键是:=
回答by Rasmus
Heres a different approach. If you have several tables of data that are not joinable, or you for some reason dont want to count all the rows at the same time but you still want them to be part off the same rowcount, you can create a table that does the job for you.
这是一种不同的方法。如果您有多个不可连接的数据表，或者您出于某种原因不想同时计算所有行，但仍希望它们属于同一行数的一部分，则可以创建一个表来完成这项工作为你。
Example:
例子：
create table #test (
rowcounter int identity,
invoicenumber varchar(30)
)
insert into #test(invoicenumber) select [column] from [Table1]
insert into #test(invoicenumber) select [column] from [Table2]
insert into #test(invoicenumber) select [column] from [Table3]
select * from #test
drop table #test
