在 Sql Server 2014 ORDER BY 子句中使用 OFFSET FETCH NEXT 返回奇怪的结果 [英] In Sql Server 2014 ORDER BY clause with OFFSET FETCH NEXT returns weird results

问题描述

我目前使用的是 Sql Server 2014 Professional，当前版本是 (12.0.4100).我有一个视图，我正在尝试选择具有特定偏移量的 10 行.
我的视图如下所示:

I am currently using Sql Server 2014 Professional and the current version is (12.0.4100). I have a View and I am trying to SELECT 10 rows with specific offset.
My View is like below:

    BeginTime   |     EndTime    | Duration |   Name   
09:00:00.0000000|16:00:00.0000000|    1     | some_name1
09:00:00.0000000|16:00:00.0000000|    2     | some_name2
09:00:00.0000000|16:00:00.0000000|    3     | some_name3
09:00:00.0000000|16:00:00.0000000|    4     | some_name4
09:00:00.0000000|16:00:00.0000000|    5     | some_name5
09:00:00.0000000|16:00:00.0000000|    6     | some_name6
09:00:00.0000000|16:00:00.0000000|    7     | some_name7

有 100 行这样的，并且在 BeginTime 和 EndTime 中都具有完全相同的值.持续时间在相关表中从 1 递增到 100.
如果查询仅:

there are 100 rows like these and all have the exact same value in BeginTime and EndTime. Duration is incremented from 1 to 100 in related table.
If query is only:

SELECT * FROM View_Name

结果集是正确的.我可以通过查看持续时间列来理解它.

如果我只想从 0 开始提取 10 行，ResultSet 是正确的，从 18 开始也是正确的.当我想从 19 或超过 19 开始提取 10 行时，ResultSet 中的 Duration 返回不相关的结果，如 Duration reversed.但它永远不会返回持续时间超过 11 的行.我用来获取特定行的查询如下:

ResultSet is correct. I can understand it by checking the duration column.

If I want to fetch only 10 rows starting from 0, ResultSet is correct and it is correct for starting from up to 18. When I want to fetch 10 rows starting from 19 or more than 19, Duration in ResultSet returns irrelevant results like Duration reversed. But it never returns the rows which has duration more than 11. The query that I used to fetch specific rows is as follows:

SELECT * FROM View_Name ORDER BY BeginTime ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

这种情况也有些奇怪；如果我指定USE master，这个问题就会消失，但是，如果我指定USE [mydb_name]，问题又会出现.
顺便说一句，我在本地电脑上使用的是SQL SERVER 2014 Professional v(12.0.2269)，上述情况下这个问题就消失了.

PS:我不能使用 USE master 因为，我正在存储过程中动态创建和列出视图.
任何帮助、答案或评论都将被接受.
谢谢！

There is also something strange in this situation; if I specify USE master, this problem disappears, but, if I specify USE [mydb_name], the problem appears again.
By the way, I am using SQL SERVER 2014 Professional v(12.0.2269) in my local pc, this problem disappears for the above situation.

PS: I can not use USE master because, I am creating and listing the view dynamically, in Stored Procedures.
Any help, answer or comment will be accepted.
Thank You!

推荐答案

文档 解释:

使用 OFFSET 和FETCH，必须满足以下条件:

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met:

...

1. ORDER BY 子句包含保证唯一的一列或列组合.

1. The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

在您的情况下，BeginTime 不是唯一的.一般的数据库——尤其是 SQL Server——没有实现稳定的排序.稳定排序是当键相同时行的顺序相同的排序.这是相当明显的，因为表和结果集表示无序集.它们没有固有的顺序.

What happens in your case is that BeginTime is not unique. Databases in general -- and SQL Server in particular -- do not implement stable sorts. A stable sort is one where the rows are in the same order when the keys are the same. This is rather obvious, because tables and result sets represent unordered sets. They have no inherent ordering.

因此，您需要一个唯一的键来使排序稳定.鉴于您的数据，这似乎是 duration、name 或两者:

So, you need a unique key to make the sort stable. Given your data, this would seem to be either duration, name, or both:

SELECT * 
ROM View_Name
ORDER BY BeginTime ASC, Duration, Name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

这篇关于在 Sql Server 2014 ORDER BY 子句中使用 OFFSET FETCH NEXT 返回奇怪的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！