ORDER BY ROW_NUMBER [英] ORDER BY ROW_NUMBER

问题描述

UPD:谢谢大家，话题结束，睡觉后我明白了一切=)

UPD: thanks for all, topic closed, after sleeping I understand everything =)

我对 OVER 子句和 ROW_NUMBER 函数的理解有问题.简单的表 - 名称和标记.我想计算每个名字的平均分.

I have a problem with understanding OVER clause and and ROW_NUMBER function. Simple table - name and mark. I want to calculate average mark for each name.

SELECT top 1 with ties name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM table 
ORDER BY AVG(mark) OVER(PARTITION BY name)

它会显示这样的东西，我明白为什么 - 这就是 ROW_NUMBER() 所做的

it will display something like this, and I understand why - that is what ROW_NUMBER() does

name|number
Pete 1
Pete 2

但是如果我写

SELECT top 1 with ties name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM table 
ORDER BY AVG(mark) OVER(PARTITION BY name), number

它会显示

name|number
Pete 1

这一次我不明白 ORDER BY 如何与 ROW_NUMBER() 函数一起工作.有人可以给我解释一下吗?

And this time I don't understand how ORDER BY works with ROW_NUMBER() function. Can somebody explain it to me?

推荐答案

您当然可以按 ROW_NUMBER 列排序，因为 SELECT 子句在 ORDER BY 子句之前计算.您可以按任何 列 或 列别名 ORDER.这就是为什么没有抛出错误消息的原因(因为它是有效的).

You can certainly order by ROW_NUMBER column because the SELECT clause is evaluated before the ORDER BY clause. You can ORDER BY any column or column alias. This is why no error message was thrown (because it is valid).

SELECT name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM @table 
ORDER BY number

评估为

name       number
---------- --------------------
John       1
pete       1
pete       2
John       2
pete       3

OP 的第二个 row_number 示例不正确.

OP's second example of row_number is not correct.

SELECT AVG(mark) OVER(PARTITION BY name), name,  ROW_NUMBER() over (PARTITION BY name ORDER BY name) as number
FROM @table 
ORDER BY AVG(mark) OVER(PARTITION BY name), number

按预期返回，因为 AVG 是第一个排序列，后跟数字.

Returns as expected because AVG is the first sort column followed by number.

            name       number
----------- ---------- --------------------
11          pete       1
11          pete       2
11          pete       3
17          John       1
17          John       2

将查询更改为 number DESC 并且 pete 仍然是第一个，但行号是降序的.

Change the query to number DESC and pete is still first however the row numbers are descending order.

            name       number
----------- ---------- --------------------
11          pete       3
11          pete       2
11          pete       1
17          John       2
17          John       1

SQL 操作顺序

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

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