SQL 手动指定 Row_Number() 的起始值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15210069/
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
Manually specify starting value for Row_Number()
提问by Huzaifa
I want to define the start of ROW_NUMBER()as 3258170instead of 1.
我想定义ROW_NUMBER()as3258170而不是1.
I am using the following SQL query
我正在使用以下 SQL 查询
SELECT ROW_NUMBER() over(order by (select 3258170)) as 'idd'.
However, the above query is not working. When I say not working I mean its executing but its not starting from 3258170. Can somebody help me?
但是，上述查询不起作用。当我说不工作时，我的意思是它正在执行，但不是从3258170. 有人可以帮助我吗？
The reason I want to specify the row number is I am inserting Rows from one table to another. In the first Table the last record's row number is 3258169and when I insert new records I want them to have the row number from 3258170.
我想指定行号的原因是我将行从一个表插入到另一个表。在第一个表中，最后一条记录的行号是3258169，当我插入新记录时，我希望它们的行号来自3258170.
回答by Gordon Linoff
Just add the value to the result of row_number():
只需将值添加到结果中row_number()：
select 3258170 - 1 + row_number() over (order by (select NULL)) as idd
The order byclause of row_number()is specifying what column is used for the order by. By specifying a constant there, you are simply saying "everything has the same value for ordering purposes". It has nothing, nothing at all to do with the first value chosen.
order byof的子句row_number()指定用于 order by 的列。通过在那里指定一个常量，您只是在说“出于排序目的，一切都具有相同的值”。它与选择的第一个值没有任何关系。
To avoid confusion, I replaced the constant value with NULL. In SQL Server, I have observedthat this assigns a sequential number without actually sorting the rows -- an observed performance advantage, but not one that I've seen documented, so we can't depend on it.
为了避免混淆，我用 NULL 替换了常量值。在 SQL Server 中，我观察到这会分配一个序列号而不实际对行进行排序——这是观察到的性能优势，但不是我所见过的记录，因此我们不能依赖它。
回答by Tom McDonough
I feel this is easier
我觉得这更容易
ROW_NUMBER() OVER(ORDER BY Field) - 1 AS FieldAlias (To start from 0)
ROW_NUMBER() OVER(ORDER BY Field) + 3258169 AS FieldAlias (To start from 3258170)
回答by Milan
Sometimes....
有时....
The ROW_NUMBER() may not be the best solution especially when there could be duplicate records in the underlying data set (for JOIN queries etc.). This may result in more rows returned than expected. You may consider creating a SEQUENCEwhich can be in some cases considered a cleaner solution. i.e.:
ROW_NUMBER() 可能不是最佳解决方案，尤其是当基础数据集中可能存在重复记录时（对于 JOIN 查询等）。这可能会导致返回的行数超出预期。您可以考虑创建一个 SEQUENCE，它在某些情况下可以被视为更简洁的解决方案。IE：
CREATE SEQUENCE myRowNumberId
START WITH 1
INCREMENT BY 1
GO
SELECT NEXT VALUE FOR myRowNumberId AS 'idd' -- your query
GO
DROP SEQUENCE myRowNumberId; -- just to clean-up after ourselves
GO
The downside is that sequences may be difficult to use in complex queries with DISTINCT, WINDOW functions etc.See the complete sequence documentation here.
缺点是序列可能难以在具有 DISTINCT、WINDOW 函数等的复杂查询中使用。请参阅此处的完整序列文档。
回答by Chloe Williams
I had a situation where I was importing a hierarchical structure into an application where a seq number had to be unique within each hierarchical level and start at 110 (for ease of subsequent manual insertion). The data beforehand looked like this...
我遇到过一种情况，我将层次结构导入到应用程序中，其中每个层次级别中的 seq 编号必须是唯一的，并且从 110 开始（以便于后续手动插入）。之前的数据是这样的……
Level Prod Type Component Quantity Seq
1 P00210005 R NZ1500 57.90000000 120
1 P00210005 C P00210005M 1.00000000 120
2 P00210005M R M/C Operation 20.00000000 110
2 P00210005M C P00210006 1.00000000 110
2 P00210005M C P00210007 1.00000000 110
I wanted the row_number() function to generate the new sequence numbers but adding 10 and then multiplying by 10 wasn't achievable as expected. To force the sequence of arithmetic functions you have to enclose the entire row_number(), and partition clause in brackets. You can only perform simple addition and substraction on the row_number() as such.
我希望 row_number() 函数生成新的序列号，但是添加 10 然后乘以 10 无法按预期实现。要强制算术函数序列，您必须将整个 row_number() 和 partition 子句括在括号中。您只能对 row_number() 执行简单的加法和减法。
So, my solution for this problem was
所以，我对这个问题的解决方案是
,10*(10+row_number() over (partition by Level order by Type desc, [Seq] asc))[NewSeq]
,10* (10+row_number() over (partition by Level order by Type desc, [Seq] asc) )[NewSeq]
Note the position of the brackets to allow the multiplication to occur after the addition.
请注意括号的位置，以允许在加法之后进行乘法。
Level Prod Type Component Quantity [Seq] [NewSeq]
1 P00210005 R NZ1500 57.90000000 120 110
1 P00210005 C P00210005M 1.00000000 120 120
2 P00210005M R M/C Operation 20.00000000 110 110
2 P00210005M C P00210006 1.00000000 110 120
2 P00210005M C P00210007 1.00000000 110 130
