关于SQL中NEWID()在ROW_NUMBER与ORDER BY中执行逻辑的疑问

你的示例查询代码：

select 
    ROW_NUMBER()OVER(PARTITION BY diff ORDER BY NEWID())
    , Account, Ticker, Client, Diff, Allocation 
from @diffAdj 
where 
    Account = @Account 
    and Ticker = @Ticker
    and RemainingShares - isnull(Allocation, 0) > 0
order by NEWID()

针对你的问题，解答如下：

• 在ROW_NUMBER()的OVER子句中按NEWID()排序生成行号时，会为每行生成一个唯一的NEWID()值，但这个值不会被自动保留到最终结果集里，只是在计算行号的过程中临时用来完成分区内的排序逻辑。
• 最终ORDER BY NEWID()阶段是重新生成全新的NEWID()值来排序，不会复用OVER子句中生成的那些值。因为每一次调用NEWID()函数都会独立生成一个全新的GUID，两个位置的调用属于完全分开的执行步骤。

如果需要让行号生成和最终排序复用同一个NEWID()值，可以通过CTE或子查询提前生成这个值，示例如下：

WITH TempData AS (
    SELECT 
        NEWID() AS GuidSort,
        Account, Ticker, Client, Diff, Allocation 
    FROM @diffAdj 
    WHERE 
        Account = @Account 
        AND Ticker = @Ticker
        AND RemainingShares - ISNULL(Allocation, 0) > 0
)
SELECT 
    ROW_NUMBER() OVER(PARTITION BY Diff ORDER BY GuidSort) AS RowNum,
    Account, Ticker, Client, Diff, Allocation 
FROM TempData
ORDER BY GuidSort

内容的提问来源于stack exchange，提问作者Yan Fung