如何在select语句中生成序列号并加1

Question

我知道我们可以在select语句中生成行号。但是行号从1开始，我需要从2开始生成。

示例

party_code
----------
R06048
R06600
R06791
(3 row(s) affected)
I want it like

party_code serial number
---------- -------------
R06048       2
R06600       3
R06791       4 

我目前正在使用以下选择语句来生成常规行号。

 SELECT party_code, ROW_NUMBER() OVER (ORDER BY party_code) AS [serial number]
FROM myTable
ORDER BY party_code

如何修改上述选择语句并从2开始？

- James123

---

哦，你会后悔的... - AakashM

你试过 ROW_NUMBER() + 1 吗？ - Austin Salonen

Answer 1

SELECT party_code, 1 + ROW_NUMBER() OVER (ORDER BY party_code) AS [serial number]
FROM myTable
ORDER BY party_code

补充一点：ROW_NUMBER() 的语法有些不寻常，并且在各种 OVER 和 PARTITION BY 子句中可能会令人困惑，但归根结底它仍然只是一个具有数值返回值的函数，该返回值可以像任何其他数字一样进行操作。

- AakashM

Answer 2

我对SQL Server并不是很了解，但以下任一方法均可：

SELECT party_code, 1 + ROW_NUMBER() OVER (ORDER BY party_code) AS [serial number]
FROM myTable
ORDER BY party_code

或者

SELECT party_code, serial_numer + 1 AS [serial number] FROM
(SELECT party_code, ROW_NUMBER() OVER (ORDER BY party_code) AS [serial number]
FROM myTable)
ORDER BY party_code

- Yahia