SQL Server 2012 分页查询

Question

我有这个表定义

CREATE TABLE [dbo].[Earnings](
    [Name] [nvarchar](50) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Earnings] [decimal](18, 2) NOT NULL
)

我有这个样本数据。

INSERT INTO [dbo].[Earnings] ([name], [Date], [Earnings])
VALUES
('person1',  datefromparts(2015, 9, 1), 9.01),
('person1',  datefromparts(2015, 10, 5), 10.05),
('person1',  datefromparts(2015, 11, 21), 11.21),
('person1',  datefromparts(2016, 1, 11), 1.12),
('person1',  datefromparts(2016, 3, 13), 3.13),
('person1',  datefromparts(2016, 5, 15), 5.15),
('person2',  datefromparts(2016, 6, 16), 6.16),
('person2',  datefromparts(2016, 8, 18), 8.18)

当我进行分页操作时，以下两个SQL语句总是返回相同的结果。我想知道这个问题的原因是什么？有哪些可能的修复方式？

SELECT  *  FROM [dbo].Earnings  order by Name   OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY ;
SELECT  *  FROM [dbo].Earnings  order by Name   OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY ;

我得到的执行结果。

Name    Date    Earnings
person1 2015-11-21 00:00:00.000 11.21
person1 2015-10-05 00:00:00.000 10.05

- Ahmad Alkhawaja

---

按照指定的排序方式应该是确定性的。 - TheGameiswar

请按名称、日期排序。 - TheGameiswar

如果你只按名称排序，SQL 可以选择任何具有该名称的前两行，并且它不必每次都遵循相同的顺序。 - Tab Alleman

Answer 1

按顺序排序应该是确定的。也就是说，如果任何列包含相同的值并在order by子句中使用，则SQL将选择/可以选择任何行。因此，为了使order by确定性，order by列应该是唯一的。

在您的情况下，请使用

SELECT  *  FROM [dbo].Earnings  order by Name,date   OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY ;
SELECT  *  FROM [dbo].Earnings  order by Name,date   OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY ;

- TheGameiswar

Answer 2

提供的答案是正确的。然而，我的建议是为您的表添加一个PrimaryKey并使用它：

CREATE TABLE [dbo].[Earnings](
    EarningId INT NOT NULL CONSTRAINT PK_Earnings PRIMARY KEY,  -- can also be added IDENTITY(1, 1)
    [Name] [nvarchar](50) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Earnings] [decimal](18, 2) NOT NULL
)

查询变得简单了，我认为也更快了，因为排序将根据聚集索引进行：

SELECT * FROM [dbo].Earnings  order by EarningId OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
SELECT * FROM [dbo].Earnings  order by EarningId OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

添加 PK 的主要优点是每个记录都可以明确地被识别。

顺便提一下，在按列排序之前，我还注意到按照 SELECT 1 排序似乎可以解决问题:

SELECT * FROM [dbo].Earnings  order by (SELECT 1) OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
SELECT * FROM [dbo].Earnings  order by (SELECT 1) EarningId OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

然而，我不会依赖这种排序方式。

- Alexei - check Codidact