|
感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！ 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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
