在 SQL Server 中对结果进行分页的最佳方法是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/109232/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the best way to paginate results in SQL Server
提问by Panagiotis Korros
What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008, 2012 if you also want to get the total number of results (before paginating)?
如果您还想获得结果总数（在分页之前），在 SQL Server 2000、2005、2008、2012 中对结果进行分页的最佳方法（性能方面）是什么？
采纳答案by mdb
Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is
获取结果总数和分页是两种不同的操作。为了这个例子，让我们假设你正在处理的查询是
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
In this case, you would determine the total number of results using:
在这种情况下，您将使用以下方法确定结果总数：
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.
...这可能看起来效率低下，但实际上非常高效，假设所有索引等都设置正确。
Next, to get actual results back in a paged fashion, the following query would be most efficient:
接下来，要以分页方式返回实际结果，以下查询将是最有效的：
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.
这将返回原始查询的第 1-19 行。这里很酷的事情，特别是对于 Web 应用程序，你不必保留任何状态，除了要返回的行号。
回答by ?zbek
Finally, Microsoft SQL Server 2012was released, I really like its simplicity for a pagination, you don't have to use complex queries like answered here.
最后，Microsoft SQL Server 2012发布了，我真的很喜欢它的简单分页，你不必使用像这里回答这样的复杂查询。
For getting the next 10 rows just run this query:
要获取接下来的 10 行，只需运行以下查询：
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Key points to consider when using it:
使用时要考虑的要点：
ORDER BYis mandatory to useOFFSET ... FETCHclause.OFFSETclause is mandatory withFETCH. You cannot useORDER BY ... FETCH.TOPcannot be combined withOFFSETandFETCHin the same query expression.
ORDER BY必须使用OFFSET ... FETCH条款。OFFSET子句是强制性的FETCH。您不能使用ORDER BY ... FETCH.TOP不能与OFFSET和FETCH在同一个查询表达式中组合。
回答by Lukas Eder
Incredibly, no other answer has mentioned the fastestway to do pagination in all SQL Server versions. Offsets can be terribly slow for large page numbers as is benchmarked here. There is an entirely different, much faster way to perform pagination in SQL. This is often called the "seek method" or "keyset pagination" as described in this blog post here.
令人难以置信的是，没有其他答案提到在所有 SQL Server 版本中进行分页的最快方法。对于大页码，偏移量可能会非常慢，正如此处的基准测试一样。有一种完全不同的、更快的方式在 SQL 中执行分页。这通常称为“搜索方法”或“键集分页”，如本博客文章中所述。
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
The "seek predicate"
“寻求谓词”
The @previousScoreand @previousPlayerIdvalues are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BYdirection is ASC, simply use >instead.
该@previousScore和@previousPlayerId值是来自前一页的最后一条记录的相应值。这允许您获取“下一个”页面。如果ORDER BY方向是ASC，只需使用>。
With the above method, you cannot immediately jump to page 4 without having first fetched the previous 40 records. But often, you do not want to jump that far anyway. Instead, you get a much faster query that might be able to fetch data in constant time, depending on your indexing. Plus, your pages remain "stable", no matter if the underlying data changes (e.g. on page 1, while you're on page 4).
使用上面的方法，你不能在没有先获取前 40 条记录的情况下立即跳转到第 4 页。但通常情况下，您无论如何都不想跳那么远。相反，您会得到一个更快的查询，它可能能够在恒定时间内获取数据，具体取决于您的索引。此外，无论基础数据是否发生变化（例如，在第 1 页上，而在第 4 页上），您的页面都将保持“稳定”。
This is the best way to implement pagination when lazy loading more data in web applications, for instance.
例如，当在 Web 应用程序中延迟加载更多数据时，这是实现分页的最佳方式。
Note, the "seek method" is also called keyset pagination.
请注意，“seek 方法”也称为keyset pagination。
Total records before pagination
分页前的总记录数
The COUNT(*) OVER()window function will help you count the number of total records "before pagination". If you're using SQL Server 2000, you will have to resort to two queries for the COUNT(*).
该COUNT(*) OVER()窗口功能将帮助你“分页之前”算的总记录数。如果您使用的是 SQL Server 2000，则必须对COUNT(*).
回答by Mohan
From SQL Server 2012, we can use OFFSETand FETCH NEXTClause to achieve the pagination.
从 SQL Server 2012 开始，我们可以使用OFFSETand FETCH NEXTClause 来实现分页。
Try this, for SQL Server:
试试这个，对于 SQL Server：
In the SQL Server 2012 a new feature was added in the ORDER BY clause, to query optimization of a set data, making work easier with data paging for anyone who writes in T-SQL as well for the entire Execution Plan in SQL Server.
Below the T-SQL script with the same logic used in the previous example.
--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012" DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 2 SET @RowspPage = 10 SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE ORDER BY ID_EXAMPLE OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY;
在 SQL Server 2012 中，在 ORDER BY 子句中添加了一项新功能，用于查询优化一组数据，使使用 T-SQL 编写的任何人以及 SQL Server 中的整个执行计划的数据分页工作更轻松。
下面的 T-SQL 脚本与上一个示例中使用的逻辑相同。
--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012" DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 2 SET @RowspPage = 10 SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE ORDER BY ID_EXAMPLE OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY;
回答by Dinesh Rabara
MSDN: ROW_NUMBER (Transact-SQL)
MSDN: ROW_NUMBER (Transact-SQL)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.
返回结果集分区内行的序列号，每个分区中的第一行从 1 开始。
以下示例按 OrderDate 的顺序返回编号为 50 到 60 的行。
WITH OrderedOrders AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber,
FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM [dbo].[vSalesPerson]
)
SELECT RowNumber,
FirstName, LastName, Sales YTD
FROM OrderedOrders
WHERE RowNumber > 50 AND RowNumber < 60;
RowNumber FirstName LastName SalesYTD
--- ----------- ---------------------- -----------------
1 Linda Mitchell 4251368.54
2 Jae Pak 4116871.22
3 Michael Blythe 3763178.17
4 Jillian Carson 3189418.36
5 Ranjit Varkey Chudukatil 3121616.32
6 José Saraiva 2604540.71
7 Shu Ito 2458535.61
8 Tsvi Reiter 2315185.61
9 Rachel Valdez 1827066.71
10 Tete Mensa-Annan 1576562.19
11 David Campbell 1573012.93
12 Garrett Vargas 1453719.46
13 Lynn Tsoflias 1421810.92
14 Pamela Ansman-Wolfe 1352577.13
回答by liggett78
There is a good overview of different paging techniques at http://www.codeproject.com/KB/aspnet/PagingLarge.aspx
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx对不同的分页技术有很好的概述
I've used ROWCOUNT method quite often mostly with SQL Server 2000 (will work with 2005 & 2008 too, just measure performance compared to ROW_NUMBER), it's lightning fast, but you need to make sure that the sorted column(s) have (mostly) unique values.
我经常在 SQL Server 2000 中使用 ROWCOUNT 方法（也适用于 2005 和 2008，只是测量与 ROW_NUMBER 相比的性能），它快如闪电，但您需要确保已排序的列（主要是) 唯一值。
回答by Thomas S. Trias
For SQL Server 2000 you can simulate ROW_NUMBER() using a table variable with an IDENTITY column:
对于 SQL Server 2000，您可以使用带有 IDENTITY 列的表变量模拟 ROW_NUMBER()：
DECLARE @pageNo int -- 1 based
DECLARE @pageSize int
SET @pageNo = 51
SET @pageSize = 20
DECLARE @firstRecord int
DECLARE @lastRecord int
SET @firstRecord = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastRecord = @firstRecord + @pageSize - 1 -- 1020
DECLARE @orderedKeys TABLE (
rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
TableKey int NOT NULL
)
SET ROWCOUNT @lastRecord
INSERT INTO @orderedKeys (TableKey) SELECT ID FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
SET ROWCOUNT 0
SELECT t.*
FROM Orders t
INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum
This approach can be extended to tables with multi-column keys, and it doesn't incur the performance overhead of using OR (which skips index usage). The downside is the amount of temporary space used up if the data set is very large and one is near the last page. I did not test cursor performance in that case, but it might be better.
这种方法可以扩展到具有多列键的表，并且不会产生使用 OR（跳过索引使用）的性能开销。缺点是如果数据集非常大并且接近最后一页，则会占用临时空间量。在这种情况下，我没有测试游标性能，但可能会更好。
Note that this approach could be optimized for the first page of data. Also, ROWCOUNT was used since TOP does not accept a variable in SQL Server 2000.
请注意，此方法可以针对第一页数据进行优化。此外，由于 TOP 不接受 SQL Server 2000 中的变量，因此使用了 ROWCOUNT。
回答by Debendra Dash
The best way for paging in sql server 2012 is by using offset and fetch next in a stored procedure. OFFSET Keyword- If we use offset with the order by clause then the query will skip the number of records we specified in OFFSET n Rows.
在 sql server 2012 中进行分页的最佳方法是在存储过程中使用 offset 和 fetch next。 OFFSET 关键字- 如果我们在 order by 子句中使用 offset，那么查询将跳过我们在 OFFSET n Rows 中指定的记录数。
FETCH NEXT Keywords- When we use Fetch Next with an order by clause only it will returns the no of rows you want to display in paging, without Offset then SQL will generate an error. here is the example given below.
FETCH NEXT 关键字- 当我们将 Fetch Next 与 order by 子句一起使用时，它将返回您想要在分页中显示的行数，如果没有 Offset，那么 SQL 将产生错误。这是下面给出的示例。
create procedure sp_paging
(
@pageno as int,
@records as int
)
as
begin
declare @offsetcount as int
set @offsetcount=(@pageno-1)*@records
select id,bs,variable from salary order by id offset @offsetcount rows fetch Next @records rows only
end
you can execute it as follow.
您可以按如下方式执行它。
exec sp_paging 2,3
回答by Ardalan Shahgholi
These are my solutions for paging the result of query in SQL server side. these approaches are different between SQL Server 2008 and 2012. Also, I have added the concept of filtering and order by with one column. It is very efficient when you are paging and filtering and ordering in your Gridview.
这些是我在 SQL 服务器端对查询结果进行分页的解决方案。这些方法在 SQL Server 2008 和 2012 之间有所不同。此外，我还添加了过滤和按一列排序的概念。当您在 Gridview 中进行分页、过滤和排序时，它非常有效。
Before testing, you have to create one sample table and insert some row in this table : (In real world you have to change Where clause considering your table fields and maybe you have some join and subquery in main part of select)
在测试之前，您必须创建一个示例表并在该表中插入一些行：（在现实世界中，您必须考虑表字段更改 Where 子句，并且可能在 select 的主要部分有一些连接和子查询）
Create Table VLT
(
ID int IDentity(1,1),
Name nvarchar(50),
Tel Varchar(20)
)
GO
Insert INTO VLT
VALUES
('NAME' + Convert(varchar(10),@@identity),'FAMIL' + Convert(varchar(10),@@identity))
GO 500000
In all of these sample, I want to query 200 rows per page and I am fetching the row for page number 1200.
在所有这些示例中，我想每页查询 200 行，并且我正在获取页码为 1200 的行。
In SQL server 2008, you can use the CTE concept. Because of that, I have written two type of query for SQL server 2008+
在 SQL Server 2008 中，您可以使用 CTE 概念。因此，我为 SQL Server 2008+ 编写了两种类型的查询
-- SQL Server 2008+
-- SQL Server 2008+
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM
(
SELECT
ROW_NUMBER()
OVER( ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN VLT.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN VLT.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN VLT.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN VLT.Tel END ASC
) AS RowNum
,*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
) AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
GO
And second solution with CTE in SQL server 2008+
和 SQL Server 2008+ 中 CTE 的第二个解决方案
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
;WITH
Data_CTE
AS
(
SELECT
ROW_NUMBER()
OVER( ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN VLT.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN VLT.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN VLT.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN VLT.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN VLT.Tel END ASC
) AS RowNum
,*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
)
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM Data_CTE AS Data
WHERE Data.RowNum > @PageSize * (@PageNumber - 1)
AND Data.RowNum <= @PageSize * @PageNumber
ORDER BY Data.RowNum
-- SQL Server 2012+
-- SQL Server 2012+
DECLARE @PageNumber Int = 1200
DECLARE @PageSize INT = 200
DECLARE @SortByField int = 1 --The field used for sort by
DECLARE @SortOrder nvarchar(255) = 'ASC' --ASC or DESC
DECLARE @FilterType nvarchar(255) = 'None' --The filter type, as defined on the client side (None/Contain/NotContain/Match/NotMatch/True/False/)
DECLARE @FilterValue nvarchar(255) = '' --The value the user gave for the filter
DECLARE @FilterColumn int = 1 --The column to wich the filter is applied, represents the column number like when we send the information.
;WITH
Data_CTE
AS
(
SELECT
*
FROM VLT
WHERE
( -- We apply the filter logic here
CASE
WHEN @FilterType = 'None' THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 1
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.ID NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 1
AND VLT.ID = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 1
AND VLT.ID <> @FilterValue THEN 1
-- Name column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 2
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Name NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 2
AND VLT.Name = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 2
AND VLT.Name <> @FilterValue THEN 1
-- Tel column filter
WHEN @FilterType = 'Contain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'NotContain' AND @FilterColumn = 3
AND ( -- In this case, when the filter value is empty, we want to show everything.
VLT.Tel NOT LIKE '%' + @FilterValue + '%'
OR
@FilterValue = ''
) THEN 1
WHEN @FilterType = 'Match' AND @FilterColumn = 3
AND VLT.Tel = @FilterValue THEN 1
WHEN @FilterType = 'NotMatch' AND @FilterColumn = 3
AND VLT.Tel <> @FilterValue THEN 1
END
) = 1
)
SELECT
Data.ID,
Data.Name,
Data.Tel
FROM Data_CTE AS Data
ORDER BY
CASE WHEN @SortByField = 1 AND @SortOrder = 'ASC'
THEN Data.ID END ASC,
CASE WHEN @SortByField = 1 AND @SortOrder = 'DESC'
THEN Data.ID END DESC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'ASC'
THEN Data.Name END ASC,
CASE WHEN @SortByField = 2 AND @SortOrder = 'DESC'
THEN Data.Name END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'ASC'
THEN Data.Tel END ASC,
CASE WHEN @SortByField = 3 AND @SortOrder = 'DESC'
THEN Data.Tel END ASC
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;
回答by fatlion
Try this approach:
试试这个方法：
SELECT TOP @offset a.*
FROM (select top @limit b.*, COUNT(*) OVER() totalrows
from TABLENAME b order by id asc) a
ORDER BY id desc;
