SQL 带有增量整数列的 MSSQL Select 语句......不是来自表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/534240/
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
MSSQL Select statement with incremental integer column... not from a table
提问by Rodrigo
I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on.
如果可能，我需要一个 t-sql 查询，该查询从任意表返回值，还返回一个增量整数列，第一行值为 1，第二行值为 2，依此类推。
This column does not actually resides in any table, and must be strictly incremental, because the ORDER BY clause could sort the rows of the table and I want the incremental row in perfect shape always...
该列实际上并不驻留在任何表中，并且必须是严格增量的，因为 ORDER BY 子句可以对表的行进行排序，我希望增量行始终处于完美形状...
Thanks in advance.
提前致谢。
--EDIT Sorry, forgot to mention, must run on SQL Server 2000
--EDIT 抱歉，忘了说，必须在 SQL Server 2000 上运行
回答by SQLMenace
For SQL 2005 and up
对于 SQL 2005 及更高版本
SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS 'rownumber',*
FROM YourTable
for 2000 you need to do something like this
2000年你需要做这样的事情
SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE
INTO #Ranks FROM YourTable WHERE 1=0
INSERT INTO #Ranks
SELECT SomeColumn FROM YourTable
ORDER BY SomeColumn
SELECT * FROM #Ranks
Order By Ranks
see also here Row Number
另见此处 行号
回答by Israel Margulies
You can start with a custom number and increment from there, for example you want to add a cheque number for each payment you can do:
您可以从自定义编号开始，然后从那里开始递增，例如，您想为您可以执行的每笔付款添加一个支票编号：
select @StartChequeNumber = 3446;
SELECT
((ROW_NUMBER() OVER(ORDER BY AnyColumn)) + @StartChequeNumber ) AS 'ChequeNumber'
,* FROM YourTable
will give the correct cheque number for each row.
将为每一行提供正确的支票号码。
回答by Misko
Try ROW_NUMBER()
尝试 ROW_NUMBER()
http://msdn.microsoft.com/en-us/library/ms186734.aspx
http://msdn.microsoft.com/en-us/library/ms186734.aspx
Example:
例子：
SELECT
col1,
col2,
ROW_NUMBER() OVER (ORDER BY col1) AS rownum
FROM tbl
回答by JohnFx
It is ugly and performs badly, but technically this works on any table with at least one unique field AND works in SQL 2000.
它很丑而且性能很差，但从技术上讲，这适用于任何具有至少一个唯一字段的表，并且适用于 SQL 2000。
SELECT (SELECT COUNT(*) FROM myTable T1 WHERE T1.UniqueField<=T2.UniqueField) as RowNum, T2.OtherField
FROM myTable T2
ORDER By T2.UniqueField
Note: If you use this approach and add a WHERE clause to the outer SELECT, you have to added it to the inner SELECT also if you want the numbers to be continuous.
注意：如果您使用这种方法并将 WHERE 子句添加到外部 SELECT 中，如果您希望数字是连续的，则还必须将其添加到内部 SELECT 中。
