在OVER ORDER BY中使用多个列

Question

在OVER ORDER BY子句中是否有指定多个列的方法？

SELECT ROW_NUMBER() OVER(ORDER BY (A.Col1)) AS ID FROM MyTable A

上述方法可以正常工作，但是尝试添加第二列并不起作用。

SELECT ROW_NUMBER() OVER(ORDER BY (A.Col1, A.Col2)) AS ID FROM MyTable A

逗号附近语法错误。

- jkh

Answer 1

问题在于列名周围有多余的括号。以下所有语句都应该可以工作：

-- The standard way
SELECT ROW_NUMBER() OVER(ORDER BY A.Col1) AS ID FROM MyTable A
SELECT ROW_NUMBER() OVER(ORDER BY A.Col1, A.Col2) AS ID FROM MyTable A

-- Works, but unnecessary
SELECT ROW_NUMBER() OVER(ORDER BY (A.Col1), (A.Col2)) AS ID FROM MyTable A

此外，当您提出 SQL 相关的问题时，应该始终指明您正在查询哪个数据库。

- We Are All Monica

Answer 2

没有括号。

SELECT ROW_NUMBER() OVER(ORDER BY A.Col1, A.Col2) AS ID FROM MyTable A 

- podiluska