如何向以下SQL添加分页 [英] How to add pagination to the following SQL

问题描述

我有以下SQL，我想知道如何分页:

I've got the following SQL and I want to know how to page it:

我有@skip和@top的变量，所以我可以翻页..

I've got variables for @skip and @top so I can page through..

SELECT ID FROM (
  SELECT COUNT(*) AS ID, -1 AS [Weight]
  FROM Employees i
    INNER JOIN #WeightedIDs w
    ON (i.ID = w.ID)
  WHERE (i.DepartmentID = 10 and i.ShiftID = 2)

  UNION ALL

  SELECT i.ID, w.[Weight]
  FROM Employees i
    INNER JOIN #WeightedIDs w
    ON (i.ID = w.ID)
  WHERE (i.DepartmentID = 10 and i.ShiftID = 2)
) x
ORDER BY x.[Weight] ASC

更新:

我有以下内容，但根本不起作用:

I've got the following but it doesn't work at all:

DECLARE @skip INT, @top INT
SET @skip = 30
SET @top = 100

;WITH PaginatedResults AS
(
  SELECT ID, w.[Weight],
    ROW_NUMBER() OVER (ORDER BY w.[Weight] ASC) AS RowNum
  FROM Employees i 
    INNER JOIN #WeightedIDs w 
    ON (i.ID = w.ID)  
  WHERE (i.DepartmentID = 10 and i.ShiftID = 2)
)
SELECT ID FROM (
  SELECT COUNT(*) AS ID, -1 AS [Weight]
  FROM Employees i 
    INNER JOIN #WeightedIDs w 
    ON (i.IssueID = w.id)  
  WHERE FlightID > 2 and IssueID > 0

  UNION ALL

  SELECT ID, [Weight]
  FROM PaginatedResults
  WHERE RowNum >= @skip AND RowNum < @skip + @top
) x
ORDER BY x.[Weight] ASC

推荐答案

此链接显示了一种很好的2008年分页机制:

This link shows a nice pagination mechanism for 2008:

等于SQL Server的LIMIT和OFFSET吗?

在SQL 2012中，有OFFSET和FETCH关键字使分页非常容易.

In SQL 2012 there are OFFSET and FETCH keywords that make it very easy to paginate.

此问题也应适用:

如何在SQL Server 2008中进行分页

这篇关于如何向以下SQL添加分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！