如何正确使用 Sql Server ROW_NUMBER 函数进行有序查询？

发布于 2024-12-08 20:07:51 字数 436 浏览 0 评论 0原文

我试图从 MSSQL 中理解 ROW_NUMBER 并取得一些经验。

我有这两个片段：

SELECT * 
    FROM 
    (
        SELECT *,
        ROW_NUMBER() OVER (order by p.DtDistribuicao) AS RowNumber
        FROM ProcessoInstanciaFonte as p
    ) as q 

WHERE q.RowNumber BETWEEN 1 AND 20;

并且

select top 20 * from dbo.ProcessoInstanciaFonte as p order by p.DtDistribuicao

它们都应该返回相同的行，但事实并非如此。问题是什么？

原文

I'm trying to understand ROW_NUMBER from MSSQL and making some experiences.

I have these two snippets:

SELECT * 
    FROM 
    (
        SELECT *,
        ROW_NUMBER() OVER (order by p.DtDistribuicao) AS RowNumber
        FROM ProcessoInstanciaFonte as p
    ) as q 

WHERE q.RowNumber BETWEEN 1 AND 20;

and

select top 20 * from dbo.ProcessoInstanciaFonte as p order by p.DtDistribuicao

They both should return the same rows but aren't. What is the problem?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

独行侠 2024-12-15 20:07:51

我猜想 p.DtDistribuicao 的值有一些联系。服务器可以自由选择任何绑定值作为“第一个”值，在这种情况下，两个不同的查询可能会给出两个不同的结果。

您可以在 ORDER BY 末尾添加一个唯一字段作为决胜局。例如，这两个查询应返回相同的行（假设您有一个名为 Id 的唯一字段）：

SELECT * 
    FROM 
    (
        SELECT *,
        ROW_NUMBER() OVER (ORDER BY p.DtDistribuicao, p.Id) AS RowNumber
        FROM ProcessoInstanciaFonte as p
    ) AS q     
WHERE q.RowNumber BETWEEN 1 AND 20;
ORDER BY q.RowNumber

SELECT TOP 20 *
FROM dbo.ProcessoInstanciaFonte AS p
ORDER BY p.DtDistribuicao, p.Id

I guess that the values of p.DtDistribuicao have some ties. The server is free to pick any of the tied values as the "first" one and the two different queries could give two different results in this case.

You could add a unique field at the end of the ORDER BY as a tie-breaker. For example, these two queries should return the same rows (assuming you have a unique field called Id):

SELECT * 
    FROM 
    (
        SELECT *,
        ROW_NUMBER() OVER (ORDER BY p.DtDistribuicao, p.Id) AS RowNumber
        FROM ProcessoInstanciaFonte as p
    ) AS q     
WHERE q.RowNumber BETWEEN 1 AND 20;
ORDER BY q.RowNumber

SELECT TOP 20 *
FROM dbo.ProcessoInstanciaFonte AS p
ORDER BY p.DtDistribuicao, p.Id

回复收藏 0 原文

~没有更多了~