OVER ORDER BY中的多个列

Is there a way to specify multiple columns in the OVER ORDER BY clause?

有没有办法在OVER ORDER BY子句中指定多个列?

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

The above works fine, but trying to add a second column does not work.

以上工作正常,但尝试添加第二列不起作用。

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

Incorrect syntax near ','.

','附近的语法不正确。

3 个解决方案

#1

The problem is the extra parentheses around the column name. These should all work:

问题是列名称周围的额外括号。这些应该都有效:

-- 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

Also, when you ask an SQL question, you should always specify which database you are querying against.

此外,当您询问SQL问题时,应始终指定要查询的数据库。

#2

No brackets.

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

#3

-6

this not possible you can see syntax of Row_Num :

这不可能你可以看到Row_Num的语法:

ROW_NUMBER ( ) 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

if use extra order by filed in group by you can do it..

如果你在小组中提交额外的订单,你可以这样做..

#1