分页表另一个Sql问题

Question

Sql Server 2005

表结构

CREATE TABLE [dbo].[Rate](
[RateID] [bigint] IDENTITY(1,1) NOT NULL,
[PairID] [bigint] NOT NULL,
[Open] [decimal](18, 4) NOT NULL,
[Close] [decimal](18, 4) NOT NULL,
[High] [decimal](18, 4) NOT NULL,
[Low] [decimal](18, 4) NOT NULL,
[Difference] [decimal](18, 4) NOT NULL,
[Average] [decimal](18, 4) NOT NULL,
[Percentage] [decimal](18, 4) NOT NULL,
[InfoDate] [datetime] NOT NULL,
[Hourly] [bit] NOT NULL,
[CaptureDateTime] [datetime] NULL,
CONSTRAINT [PK_Rate] PRIMARY KEY CLUSTERED 
(
[RateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  =   ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

我正在使用分页来检索表

Select  * from(
SELECT
    (ROW_NUMBER()OVER (ORDER BY InfoDate ASC)) AS RowNo,
    [RateID],
    [PairID],
    [Open],
    [Close],
    [High],
    [Low],
    [InfoDate],
    [CaptureDateTime]
From Rate
) AS T
WHERE t.RowNo 
BETWEEN 200*@PageNumber AND 200 * (@PageNumber+1)-1
ORDER BY RowNo DESC

[问题] 我需要查询，该查询将给出此表

PageNo、StartIndex（该页面中的第一个 RateId）、EndIndex（该页面中的最后一个 RateId）、StartDate（该页面中的第一个 infoDate）、EndDate（该页面中的最后一个 infoDate）。

Answer 1

您可以尝试执行以下操作：

DECLARE @PageSize INT = 200

;WITH PagingInfo AS
(
    SELECT
        RateID, InfoDate,
        ROW_NUMBER() OVER (ORDER BY InfoDate) AS RowNo
    FROM 
        dbo.Rate
),
Pages AS
(
    SELECT     
       RateID,
       InfoDate,
       RowNo,
       ((RowNo - 1) / @PageSize) AS PageNo
    FROM PagingInfo
)
SELECT     
    p.PageNo,
    (SELECT RateID FROM Pages p2 WHERE p2.PageNo = p.PageNo AND p2.RowNo % @PageSize = 1) AS 'First RateID',
    (SELECT RateID FROM Pages p2 WHERE p2.PageNo = p.PageNo AND p2.RowNo % @PageSize = 0) AS 'Last RateID',
    (SELECT InfoDate FROM Pages p2 WHERE p2.PageNo = p.PageNo AND p2.RowNo % @PageSize = 1) AS 'First InfoDate',
    (SELECT InfoDate FROM Pages p2 WHERE p2.PageNo = p.PageNo AND p2.RowNo % @PageSize = 0) AS 'Last InfoDate'
FROM Pages p 
WHERE p.RowNo % @PageSize = 0

两个 CTE 基本上与您执行的操作相同 - 它们提供数据分页。第二个 CTE Pages 还提供每行的页码。

从这些 CTE 中，我选择相关内容 - 页码（来自 Pages），以及每个页面的第一个和最后一个 RateID 和 InfoDate 。这是有效的，因为：

• 每个页面的第一行的行号比页面大小的倍数高 1，例如 1、201、401、601 等 - 因此整数除以页面大小的余数将是始终为 1

• 每页的最后一行都有一个可被页面大小整除的行号（例如 200、400、600 等），因此整数除法的余数为0

根据此信息，我可以从每个页面的 Pages CTE 中挑选出第一个和最后一个 RateID 和 InfoDate。

Answer 2

像这样的东西（未经测试）。

SELECT @PageNumber as PageNo,
       min(RateID) as StartIndex,
       max(RateID) as EndIndex,
       min(infoDate) as StartDate,
       max(infoDate) as EndDate 
FROM (
      SELECT
          (ROW_NUMBER()OVER (ORDER BY InfoDate ASC)) AS RowNo,
          [RateID],
          [InfoDate]
      FROM Rate
     ) AS T
WHERE t.RowNo BETWEEN 200*@PageNumber AND 200 * (@PageNumber+1)-1

如果您想要一个查询中的所有页面，您可以使用它。

SELECT PageNo + 1 as PageNo,
       MIN(RateID) as StartIndex,
       MAX(RateID) as EndIndex,
       MIN(infoDate) as StartDate,
       MAX(infoDate) as EndDate 
FROM (
      SELECT
          (ROW_NUMBER() OVER(ORDER BY InfoDate ASC) - 1) / @PageSize AS PageNo,
          [RateID],
          [InfoDate]
      FROM Rate
     ) AS T
GROUP BY PageNo
ORDER BY PageNo