SQL 窗口函数只能出现在 SELECT 或 ORDER BY 子句中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14111321/
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
Windowed functions can only appear in the SELECT or ORDER BY clauses
提问by Mari
Can anyone explain why can't we use windowed functions in group byclause and why it's allowed only in SELECTand ORDER BY
谁能解释为什么我们不能在group by子句中使用窗口函数以及为什么它只允许在SELECT和ORDER BY
I was trying to group the records based on row_number()and a column in SQL Server as like this:
我试图根据row_number()SQL Server 中的列对记录进行分组，如下所示：
SELECT Invoice
from table1
group by row_number() over(order by Invoice),Invoice
I am getting an error
我收到一个错误
Windowed functions can only appear in the SELECT or ORDER BY
窗口函数只能出现在 SELECT 或 ORDER BY
I can select this row_number()in SELECT clause but I want to know why can't we use it group by?
我可以row_number()在 SELECT 子句中选择它，但我想知道为什么我们不能使用它 group by ？
采纳答案by Martin Smith
Windowed functions are defined in the ANSI spec to logically execute after the processing of GROUP BY, HAVING, WHERE.
窗口函数在ANSI规范定义的处理之后执行逻辑GROUP BY，HAVING，WHERE。
To be more specific they are allowed at steps 5.1 and 6 in the Logical Query Processing flow chart here.
更具体地说，在此处的逻辑查询处理流程图中的步骤 5.1 和 6 中允许使用它们。
I suppose they could have defined it another way and allowed GROUP BY, WHERE, HAVINGto use window functions with the window being the logical result set at the start of that phase but suppose they had and we were allowed to construct queries such as
我想他们可以这样来定义它的另一种方式，并允许GROUP BY，WHERE，HAVING使用窗口函数与窗口是合乎逻辑的结果集在该阶段的开始，但假设他们有和我们被允许构造查询如
SELECT a,
b,
NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect
FROM YourTable
WHERE NTILE(2) OVER (PARTITION BY a ORDER BY b) > 1
GROUP BY a,
b,
NTILE(2) OVER (PARTITION BY a ORDER BY b)
HAVING NTILE(2) OVER (PARTITION BY a ORDER BY b) = 1
With four different logical windows in play good luck working out what the result of this would be! Also what if in the HAVINGyou actually wanted to filter by the expression from the GROUP BYlevel above rather than with the window of rows being the result after the GROUP BY?
使用四个不同的逻辑窗口，祝你好运，计算出结果会是什么！另外，如果在 中HAVING您实际上想通过来自GROUP BY上面级别的表达式进行过滤，而不是将行窗口作为GROUP BY?之后的结果呢？
The CTE version is more verbose but also more explicit and easier to follow.
CTE 版本更冗长，但也更明确且更易于遵循。
WITH T1 AS
(
SELECT a,
b,
NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForWhere
FROM YourTable
), T2 AS
(
SELECT a,
b,
NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForGroupBy
FROM T1
WHERE NtileForWhere > 1
), T3 AS
(
SELECT a,
b,
NtileForGroupBy,
NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForHaving
FROM T2
GROUP BY a,b, NtileForGroupBy
)
SELECT a,
b,
NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect
FROM T3
WHERE NtileForHaving = 1
As these are all defined in the SELECTstatement and are aliased it is easily achievable to disambiguate results from different levels e.g. simply by switching WHERE NtileForHaving = 1to NtileForGroupBy = 1
由于这些都在SELECT语句中定义并具有别名，因此很容易消除不同级别的结果歧义，例如只需切换WHERE NtileForHaving = 1到NtileForGroupBy = 1
回答by Andomar
You can work around that by placing the window function in a subquery:
您可以通过将窗口函数放在子查询中来解决这个问题：
select invoice
, rn
from (
select Invoice
, row_number() over(order by Invoice) as rn
from Table1
) as SubQueryAlias
group by
invoice
, rn
