sqlserver 支持定位当前页,自定义排序的分页SQL(拒绝动态SQL)
时间:2024-01-16 03:58:06
1,场景:根据学生编号查询,返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。
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
2,简单条件的,动态where语句(关于Like查询的动态where,建议使用笨办法做)
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
标签:自定义排序,分页SQL
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python中顺序表原理与实现方法详解
2023-03-24 22:57:48
使用python-Jenkins批量创建及修改jobs操作
2023-07-01 01:53:11
![](https://img.aspxhome.com/file/2023/4/82514_0s.jpg)
python中的字符串切割 maxsplit
2022-04-16 14:35:35
webpack css加载和图片加载的方法示例
2024-04-22 13:02:13
![](https://img.aspxhome.com/file/2023/9/135969_0s.png)
python解析xml文件操作实例
2022-01-02 10:39:13
Django Serializer HiddenField隐藏字段实例
2022-10-29 02:39:07
YUI学习笔记(4)
2009-03-10 18:25:00
Python中的if判断语句中包含or问题
2021-12-03 14:30:21
![](https://img.aspxhome.com/file/2023/9/110219_0s.png)
基于Python和Scikit-Learn的机器学习探索
2023-12-25 00:41:10
Oracle备库宕机启动的完美解决方案
2023-07-20 05:28:34
![](https://img.aspxhome.com/file/2023/9/64079_0s.png)
vue中的mixins混入使用方法
2023-07-02 17:01:47
一文带你吃透Python中的日期时间模块
2023-01-11 19:33:32
![](https://img.aspxhome.com/file/2023/0/103670_0s.png)
查看已安装tensorflow版本的方法示例
2021-09-21 03:49:11
![](https://img.aspxhome.com/file/2023/0/75170_0s.png)
openCV显著性检测的使用
2022-10-20 12:25:02
vue中的ref和$refs的使用
2024-05-13 09:08:44
使用tensorflow实现矩阵分解方式
2022-04-17 17:01:17
![](https://img.aspxhome.com/file/2023/7/89347_0s.jpg)
Python实现爬取需要登录的网站完整示例
2022-08-09 11:40:33
MySql多表链接查询详细教程
2024-01-16 06:03:33
![](https://img.aspxhome.com/file/2023/2/68582_0s.png)
asp.net下利用js实现返回上一页的实现方法小集
2024-05-28 15:37:22
Python的Django框架中使用SQLAlchemy操作数据库的教程
2021-10-28 11:41:09