任何人都可以解释为什么我们不能在group by子句中使用窗口函数,以及为什么只允许在SELECT和ORDER BY中使用
我正在尝试根据row_number()和sql Server中的列分组记录,如下所示：
SELECT Invoice from table1 group by row_number() over(order by Invoice),Invoice
我收到错误
Windowed functions can only appear in the SELECT or ORDER BY
我可以在SELECT子句中选择这个row_number(),但是我想知道为什么我们不能使用它group by？
有什么建议么
提前感谢,新年快乐！
解决方法
窗口函数在ANSI规范中定义,在GROUP BY,HAVING,WHERE的处理后逻辑执行.
更具体地说,它们允许在Logical Query Processing flow chart here的步骤5.1和6中.
我想他们可以以另一种方式定义它,并允许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,NTILE(2) OVER (PARTITION BY a ORDER BY b) HAVING NTILE(2) OVER (PARTITION BY a ORDER BY b) = 1
有四个不同的逻辑窗口,运气好运气,这是什么结果！另外如果在HAVING中实际上想要通过GROUP BY级别的表达式进行过滤,而不是GROUP BY之后的结果行的窗口呢？
CTE版本更详细,但更明确,更容易遵循.
WITH T1 AS ( SELECT a,NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForWhere FROM YourTable ),T2 AS ( SELECT a,NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForGroupBy FROM T1 WHERE NtileForWhere > 1 ),T3 AS ( SELECT a,NtileForGroupBy,NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForHaving FROM T2 GROUP BY a,NtileForGroupBy ) SELECT a,NTILE(2) OVER (PARTITION BY a ORDER BY b) AS NtileForSelect FROM T3 WHERE NtileForHaving = 1
由于这些都在SELECT语句中定义并且是别名的,因此可以很容易地实现不同级别的结果歧义,例如只需将WHERE NtileForHaving = 1切换到NtileForGroupBy = 1即可