sql - ORDER BY ROW_NUMBER

Question

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

我在理解 OVER 子句和 ROW_NUMBER 函数时遇到问题。简单的表 - 名称和标记。我想计算每个名字的平均分数。

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() 所做的

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() 函数一起工作。有人可以向我解释一下吗？

Answer 1

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

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 示例不正确。

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 是第一个排序列，后跟数字。

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

将查询更改为 数字 DESC 并且 pete 仍然是第一个，但是行号是降序排列的。

            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