为什么 ROW_NUMBER OVER（ORDER BY 列）返回的结果顺序与 ORDER BY 列不同？
我使用的是 SQL Server 2008，使用 NHibernate 作为持久层（尽管我相信这个问题纯粹是 SQL）。
我将问题归结为以下 SQL 语句：
SELECT TOP 2
this_.Id as Id36_0_,
this_.Name as Name36_0_,
ROW_NUMBER() OVER (ORDER BY this_.IsActive) as MyOrder
FROM Campsites this_
ORDER BY this_.IsActive /* a bit field */
这是 NH 生成的用于检索分页结果集的查询的一部分。上面的语句给出了以下结果：
Id36_0_ Name36_0_ MyOrder
9806 Camping A Cassagnau 1
8869 Camping a la ferme La Bergamotte 2
但是，如果我省略 ROW_NUMBER() OVER (ORDER BY this_.IsActive) - 这是 NH 在第一页上检索结果时生成的 - 我会在结果中得到两个完全不同的表条目：
SELECT TOP 2
this_.Id as Id36_0_,
this_.Name as Name36_0_
/* ROW_NUMBER() OVER(ORDER BY this_.IsActive) as MyOrder */
FROM Campsites this_
ORDER BY this_.IsActive /* a bit field */
返回
Id36_0_ Name36_0_
22876 Centro Vacanze Pra delle Torri
22135 Molecaten Park Napoleon Hoeve
这让我完全困惑，并导致我们的应用程序中出现一个错误，我在搜索的第一页和第二页上得到与第一个元素相同的营地条目。
为什么相同的 ORDER BY 子句在 ROW_NUMBER OVER() 表达式中的工作方式不同？
I'm on SQL Server 2008, using NHibernate as persistence layer (although this problem is purely SQL, I believe).
I've boiled down my problem to the following SQL statement:
SELECT TOP 2
this_.Id as Id36_0_,
this_.Name as Name36_0_,
ROW_NUMBER() OVER (ORDER BY this_.IsActive) as MyOrder
FROM Campsites this_
ORDER BY this_.IsActive /* a bit field */
This is part of the query that NH generates for retrieving a paged result set. The above statement gives me the following result:
Id36_0_ Name36_0_ MyOrder
9806 Camping A Cassagnau 1
8869 Camping a la ferme La Bergamotte 2
However, if I omit the ROW_NUMBER() OVER (ORDER BY this_.IsActive) - which is what NH generates for retrieving results on the first page - I get two completely different table entries in my result:
SELECT TOP 2
this_.Id as Id36_0_,
this_.Name as Name36_0_
/* ROW_NUMBER() OVER(ORDER BY this_.IsActive) as MyOrder */
FROM Campsites this_
ORDER BY this_.IsActive /* a bit field */
returns
Id36_0_ Name36_0_
22876 Centro Vacanze Pra delle Torri
22135 Molecaten Park Napoleon Hoeve
This completely confuses me and leads to a bug in our app where I get the same Campsite entry as the first element on the first AND the second page of our search.
Why does the same ORDER BY clause work differently inside the ROW_NUMBER OVER() expression?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
ORDER BY this_.IsActive /* 一个位字段 */
因为这是一个位字段，所以它只能是 0 或 1...我假设你有很多行，这个位字段是 0 或 1，按这样排序并不会使从某种意义上说，如果 90% 处于活动状态怎么办...在这种情况下，您实际上并没有正确订购，因为您没有第二次订购。
你为什么不选择一些独特的东西......也许是他的_.Name 例如
或者这个怎么样？
ORDER BY this_.IsActive /* a bit field */
since that is a bit field it can only be 0 or 1...I assume you have many rows with this bit field being 0 or 1 ordering it by that doesn't make sense, what if 90% is active...you are not really ordering correctly in that case because you don't have a second ordering.
why don't you pick something that is unique...maybe his_.Name for example
or what about this?
在这两种情况下它基本上都是随机的，因为位字段不利于任何排序（如 SQL Menace 所指出的）。它们由 DB Engine 单独评估，因为它们彼此无关。
注意：
It's basically random in both instances because a bit field is bad for any ordering (as SQL Menace noted). They are separately evaluated by the DB Engine because they have nothing to do with each other.
Note:
您希望结果也按名称排序，但 ORDER BY 子句中只有 IsActive。
SELECT 的本质是基于集合的，因此如果您没有显式定义顺序，则不应依赖查询的任意（但看似正确）排序的结果。
You expect the result to be ordered by Name too, but only have IsActive in the ORDER BY clause.
The nature of SELECT is set-based, so you should not rely on arbitrary (but seemingly correct) ordered results of your query if you do not explicitly define the order.