SQL Server 支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)

2022-11-12 09:53:50

内容摘要

这篇文章主要为大家详细介绍了SQL Server 支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)，具有一定的参考价值，可以用来参考一下。对此感兴趣的朋友，看看idc笔记做的技术

文章正文

这篇文章主要为大家详细介绍了SQL Server 支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。

代码如下:

<code> 
CREATE PROC [dbo].[Sp_testpagerandsorting] (@GroupID INT, 
@CurrentId INT, 
@TimeFrom DATETIME, 
@TimeTo DATETIME, 
@OrderBy CHAR(50), 
@PageSize INT, 
@CurrentPage INT) 
AS 
SET nocount ON 
BEGIN 
DECLARE @StartNumber INT, 
@EndNumber INT, 
@CurrentIdRowNumber INT, 
@RecordCount INT, 
@EndPageIndex INT 
DECLARE @RowNumberTable TABLE ( 
rownumber INT IDENTITY (1, 1), 
id INT ) 
--step 1: Build sort id list ------------------------------------------------------- 
INSERT INTO @RowNumberTable 
(id) 
SELECT sm.id AS id 
FROM dbo.test sm WITH (nolock) 
WHERE indate BETWEEN Coalesce(@TimeFrom, indate) AND 
Coalesce(@TimeTo, indate) 
AND sm.groupid = @GroupID 
ORDER BY CASE 
WHEN @OrderBy = 'InDate desc' THEN ( Row_number() OVER (ORDER BY indate DESC)) 
WHEN @OrderBy = 'InDate asc' THEN (Row_number() OVER (ORDER BY indate ASC)) 
WHEN @OrderBy = 'Id asc' THEN (Row_number() OVER (ORDER BY sm.id ASC)) 
WHEN @OrderBy = 'Id desc' THEN (Row_number() OVER (ORDER BY sm.id DESC)) 
WHEN @OrderBy = 'Name asc' THEN (Row_number() OVER (ORDER BY sm.name ASC)) 
WHEN @OrderBy = 'Name desc' THEN (Row_number() OVER (ORDER BY sm.name DESC) ) 
END
--step 2: Reset page index with current id ----------------------------------------- 
IF @CurrentIdNumber > 0 
BEGIN 
SELECT TOP 1 @CurrentIdRowNumber = rownumber 
FROM @RowNumberTable 
WHERE id = @CurrentIdNumber 
IF @CurrentIdRowNumber > 0 
BEGIN 
IF @CurrentPage = 0 
BEGIN 
SET @CurrentPage = Ceiling(CAST(@CurrentIdRowNumber AS DECIMAL) / CAST (@PageSize AS DECIMAL)) 
END
END
END
ELSE 
BEGIN 
IF @CurrentPage = 0 
BEGIN 
SET @CurrentPage = 1 
END
END
--step 3: Set recordCount ----------------------------------------- 
SELECT @RecordCount = COUNT(1) 
FROM @RowNumberTable 
--step 4: Calc startNumber & endNumber ----------------------------------------- 
SELECT @StartNumber = @PageSize * ( @CurrentPage - 1 ), 
@EndNumber = @PageSize * ( @CurrentPage - 1 ) + @pageSize, 
@EndPageIndex = Ceiling(CAST(@RecordCount AS DECIMAL) / CAST(@PageSize AS DECIMAL)) 
IF @CurrentPage = @EndPageIndex 
BEGIN 
SET @EndNumber = @RecordCount 
END
--step 5: Get sorted id of current page ----------------------------------------- 
;WITH a 
AS (SELECT TOP (@EndNumber - @StartNumber) id, 
rownumber 
FROM (SELECT TOP (@EndNumber) id, 
rownumber 
FROM @RowNumberTable) AS b 
ORDER BY rownumber DESC) 
--step 6: Return current page idList ------------------------------------------------------- 
SELECT [ID], 
[GroupID] [Name], 
[Address] 
FROM dbo.test sm WITH(nolock) 
INNER JOIN a 
ON a.id = sm.id 
ORDER BY a.rownumber 
-- step 7:return current page & record count ---------------------------------- 
SELECT @CurrentPage AS currentpage, 
@RecordCount AS recordcount 
END
</code>

2,简单条件的,动态where语句(关于Like查询的动态where，建议使用笨办法做)

代码如下:

<code> 
CREATE PROC [dbo].[Getstudentlistbycondition] @Name NVARCHAR(20), 
@Class INT 
AS 
SET nocount ON 
BEGIN 
BEGIN 
SELECT [Name], 
[class] 
FROM [testtable] 
WHERE [Class] = CASE 
WHEN @Class > 0 THEN @Class ELSE [Class] END
AND [name] = CASE 
WHEN @Name <> '' THEN @Name ELSE [Name] END
END
END
</code>

注：关于SQL Server 支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)的内容就先介绍到这里，更多相关文章的可以留意

代码注释

上一篇 SQL Server 自动备份所有数据库的SQL 下一篇将Sql Server对象的当前拥有者更改成目标拥有者

作者：喵哥笔记

学的不仅是技术，更是梦想！