OVER ORDER BY 中的多列 [英] Multiple columns in OVER ORDER BY

问题描述

有没有办法在 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

<块引用>

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

解决方案

问题在于列名周围的额外括号.这些都应该有效:

-- 标准方式SELECT ROW_NUMBER() OVER(ORDER BY A.Col1) AS ID FROM MyTable ASELECT ROW_NUMBER() OVER(ORDER BY A.Col1, A.Col2) AS ID FROM MyTable A-- 有效，但没有必要SELECT ROW_NUMBER() OVER(ORDER BY (A.Col1), (A.Col2)) AS ID FROM MyTable A

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

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

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 ','.

解决方案

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.

这篇关于OVER ORDER BY 中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！