主頁(yè) > 知識(shí)庫(kù) > sqlserver 支持定位當(dāng)前頁(yè),自定義排序的分頁(yè)SQL(拒絕動(dòng)態(tài)SQL)

sqlserver 支持定位當(dāng)前頁(yè),自定義排序的分頁(yè)SQL(拒絕動(dòng)態(tài)SQL)

熱門(mén)標(biāo)簽:一個(gè)導(dǎo)航軟件能用幾個(gè)地圖標(biāo)注點(diǎn) 百度地圖標(biāo)注改顏色 臨沂智能電銷機(jī)器人加盟哪家好 申請(qǐng)400電話在哪辦理流程 貴州房產(chǎn)智能外呼系統(tǒng)供應(yīng)商 鎮(zhèn)江網(wǎng)路外呼系統(tǒng)供應(yīng)商 小e電話機(jī)器人 電銷外呼有錄音系統(tǒng)有哪些 外呼運(yùn)營(yíng)商線路收費(fèi)
1,場(chǎng)景:根據(jù)學(xué)生編號(hào)查詢,返回該學(xué)生所在班級(jí)的所有學(xué)生。支持分頁(yè)、自定義排序及結(jié)果集自動(dòng)定位到查詢條件的學(xué)生編號(hào)所在頁(yè)。
復(fù)制代碼 代碼如下:

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,簡(jiǎn)單條件的,動(dòng)態(tài)where語(yǔ)句(關(guān)于Like查詢的動(dòng)態(tài)where,建議使用笨辦法做)
復(fù)制代碼 代碼如下:

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
您可能感興趣的文章:
  • 基于Oracle的高性能動(dòng)態(tài)SQL程序開(kāi)發(fā)
  • 為什么ASP中執(zhí)行動(dòng)態(tài)SQL總報(bào)錯(cuò)誤信息?提示語(yǔ)句語(yǔ)法錯(cuò)誤
  • 批處理 動(dòng)態(tài)sql
  • 動(dòng)態(tài)SQL語(yǔ)句使用心得
  • 編程經(jīng)驗(yàn)點(diǎn)滴 動(dòng)態(tài)SQL的拼接技巧
  • oracle中動(dòng)態(tài)SQL使用詳細(xì)介紹
  • mysql存儲(chǔ)過(guò)程 在動(dòng)態(tài)SQL內(nèi)獲取返回值的方法詳解
  • MySQL 存儲(chǔ)過(guò)程中執(zhí)行動(dòng)態(tài)SQL語(yǔ)句的方法
  • mybatis的動(dòng)態(tài)sql詳解(精)

標(biāo)簽:三明 嘉興 澳門(mén) 晉城 保定 合肥 日照 延邊

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《sqlserver 支持定位當(dāng)前頁(yè),自定義排序的分頁(yè)SQL(拒絕動(dòng)態(tài)SQL)》,本文關(guān)鍵詞  sqlserver,支持,定位,當(dāng)前,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《sqlserver 支持定位當(dāng)前頁(yè),自定義排序的分頁(yè)SQL(拒絕動(dòng)態(tài)SQL)》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于sqlserver 支持定位當(dāng)前頁(yè),自定義排序的分頁(yè)SQL(拒絕動(dòng)態(tài)SQL)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章