SQL 如何在tsql中生成连续的行号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26160970/
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
How to generate sequential row number in tsql?
提问by manjuvreddy
I have a requirement in a report to show alternate colors in row and for this I need to generate sequential numbers in a SQL Select statement (see example below) to use later while displaying rows. I am trying row_number and some other techniques its not working. This should not be done using script, I should be able to generate within Select statement. Appreciate any help.
我在报告中需要在行中显示替代颜色，为此我需要在 SQL Select 语句中生成序列号（请参见下面的示例），以便稍后在显示行时使用。我正在尝试 row_number 和其他一些技术，但它不起作用。这不应该使用脚本来完成，我应该能够在 Select 语句中生成。感谢任何帮助。
RowNumber - 1, Otherdata - Something1
RowNumber - 2, Otherdata - Something2
RowNumber - 3, Otherdata - Something3
RowNumber - 4, Otherdata - Something4
RowNumber - 5, Otherdata - Something5
回答by Waqar
There is no need to avoid Analytic Functions if your database supports them e.g ROW_NUMBER()
如果您的数据库支持分析函数，则无需避免使用它们，例如 ROW_NUMBER()
SELECT
ROW_NUMBER() OVER (ORDER BY [<PRIMARYKEY_COLUMN_NAME>]) AS Number
FROM
[<TABLE_NAME>]
The syntax is Func([ arguments ]) OVER (analytic_clause)you need to focus on OVER (). This last parentheses make partition(s) of your rows and apply the Func() on these partitions one by one. In above code we have only single set/partition of rows. Therefore the generated sequence is for all the rows.
语法是Func([ arguments ]) OVER (analytic_clause)你需要关注OVER()。最后一个括号对您的行进行分区，并在这些分区上一一应用 Func()。在上面的代码中，我们只有一组/分区的行。因此，生成的序列适用于所有行。
You can make multiple set of your data and generate sequence number for each one in a single go. For example if you need generate sequence number for all the set of rows those have same categoryId. You just need to add Partition Byclause like this (PARTITION BY categoryId ORDER BY [<PRIMARYKEY_COLUMN_NAME>]).
您可以一次性制作多组数据并为每组数据生成序列号。例如，如果您需要为所有具有相同 categoryId 的行集生成序列号。你只需要Partition By像这样添加子句(PARTITION BY categoryId ORDER BY [<PRIMARYKEY_COLUMN_NAME>])。
Remember that after FROMyou can also use another extra ORDER BYto sort your data differently. But it has no effect on the OVER ()
请记住，FROM您还可以使用其他额外功能ORDER BY以不同方式对数据进行排序。但是对OVER()没有影响
回答by Charles Bretana
If sort column contains unique values, you can also do it without the new built-in Row_Number() function, by using a subquery based on a sort column.
如果排序列包含唯一值，您也可以在没有新的内置 Row_Number() 函数的情况下通过使用基于排序列的子查询来完成。
Select [other stuff],
(Select count(*) From table
where sortCol < a.sortCol) rowNum
From table a
Order by sortCol
change <to <=to start counting at 1 instead of 0
更改<为<=从 1 而不是 0 开始计数
