今天對分頁語句做一個(gè)簡單的總結(jié),他們大同小異的,只要理解其中一個(gè)其他的就很好理解了。
使用top選項(xiàng)
使用max函數(shù)
這種方法的前提是有唯一值的一個(gè)列。
使用row_number()
where rownumber between 10 and 20
select top 10 * from ( select ROW_NUMBER() over ( order by orderid) as rownumber, * from Orders) myresult
where rownumber > 10
with OrderedResult as (
select * ,ROW_NUMBER() over ( Order by orderid) as rownumber from Orders)
select * from OrderedResult where rownumber between 10 and 20
使用rowcount設(shè)置
declare @first_id varchar ( 18 ), @startrow int
set rowcount 10
select @first_id = orderid from Orders order by orderid
select * from Orders where orderid > @first_id order by orderid
set rowcount 0
end
使用臨時(shí)表
declare @pagelowerbound int
declare @pageupperbound int
set @pagelowerbound = 10
set @pageupperbound = 20
create table #pageindex( [ indexid ] int identity ( 1 , 1 ) not null , [ id ] varchar ( 18 ))
declare @sql nvarchar ( 2000 )
set @sql = ' insert into #pageindex([id]) select top ' + CONVERT ( nvarchar , @pageupperbound )
set @sql = @sql + ' orderid from Orders '
execute sp_executesql @sql
select a. * from Orders a inner join #pageindex b on a.orderid = b.id where b.indexid > @pagelowerbound order by b.indexid
drop table #pageindex
end
使用動(dòng)態(tài)存SQL語句
一個(gè)分頁存儲(chǔ)過程,支持多表連接的情況。原理還是使用主鍵。
USE [ PressErp ] GO /* ***** Object: StoredProcedure [dbo].[UP_GetRecordByPageIndex] Script Date: 05/06/2013 22:00:50 ***** */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- 用途:分頁存儲(chǔ)過程(對有主鍵的表效率極高) ALTER PROCEDURE [ dbo ] . [ UP_GetRecordByPageIndex ] @tblName varchar ( 255 ), -- 表名 @fldName varchar ( 255 ), -- 主鍵字段名 @PageSize int = 10 , -- 頁尺寸 @PageIndex int = 1 , -- 頁碼 @IsReCount bit = 0 , -- 返回記錄總數(shù), 非 0 值則返回 @OrderType bit = 0 , -- 設(shè)置排序類型, 非 0 值則降序 @strWhere varchar ( 1000 ) = '' -- 查詢條件 (注意: 不要加 where) AS declare @strSQL varchar ( 6000 ) -- 主語句 declare @strTmp varchar ( 100 ) -- 臨時(shí)變量(查詢條件過長時(shí)可能會(huì)出錯(cuò),可修改100為1000) declare @strOrder varchar ( 400 ) -- 排序類型 if @OrderType != 0 begin set @strTmp = ' <(select min ' set @strOrder = ' order by [ ' + @fldName + ' ] desc ' end else begin set @strTmp = ' >(select max ' set @strOrder = ' order by [ ' + @fldName + ' ] asc ' end set @strSQL = ' select top ' + str ( @PageSize ) + ' * from [ ' + @tblName + ' ] where [ ' + @fldName + ' ] ' + @strTmp + ' ([ ' + @fldName + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ ' + @fldName + ' ] from [ ' + @tblName + ' ] ' + @strOrder + ' ) as tblTmp) ' + @strOrder if @strWhere != '' set @strSQL = ' select top ' + str ( @PageSize ) + ' * from [ ' + @tblName + ' ] where [ ' + @fldName + ' ] ' + @strTmp + ' ([ ' + @fldName + ' ]) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' [ ' + @fldName + ' ] from [ ' + @tblName + ' ] where ' + @strWhere + ' ' + @strOrder + ' ) as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = ' select top ' + str ( @PageSize ) + ' * from [ ' + @tblName + ' ] ' + @strTmp + ' ' + @strOrder end if @IsReCount != 0 set @strSQL = ' select count(*) as Total from [ ' + @tblName + ' ] ' + ' where ' + @strWhere exec ( @strSQL )
?
USE [ Press ] GO /* ***** Object: StoredProcedure [dbo].[UP_GetRecordByPage] Script Date: 09/16/2012 00:26:26 ***** */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- 用途:支持任意排序的分頁存儲(chǔ)過程 CREATE PROCEDURE [ dbo ] . [ UP_GetRecordByPage ] @tblName1 varchar ( 255 ), -- 主表名 School @tblName2 varchar ( 500 ), -- 次表以及連接School left join City on School.CityID=City.ID left join County on School.CountyID=County.ID @fldName varchar ( 255 ), -- 顯示字段名 @OrderfldName varchar ( 255 ), -- 排序字段名,只能與一個(gè)排序字段名School.ID @PageSize int = 10 , -- 頁尺寸 @PageIndex int = 1 , -- 頁碼 @IsReCount bit = 1 , -- 返回記錄總數(shù), 非 0 值則返回 @OrderType bit = 0 , -- 設(shè)置排序類型, 非 0 值則降序 @strWhere varchar ( 1000 ) = '' , -- 查詢條件 (注意: 不要加 where) 'School.SchoolName like ''%浙江%''' @IsPrint bit = 0 -- 是否打印 AS declare @strSQL varchar ( 6000 ) -- 主語句 declare @strTmp varchar ( 1000 ) -- 臨時(shí)變量(查詢條件過長時(shí)可能會(huì)出錯(cuò),可修改100為1000) declare @strOrder varchar ( 400 ) -- 排序類型 if @OrderType != 0 begin set @strTmp = ' <(select min ' set @strOrder = ' order by ' + @OrderfldName + ' desc ' end else begin set @strTmp = ' >(select max ' set @strOrder = ' order by ' + @OrderfldName + ' asc ' end set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @fldName + ' from ' + @tblName2 + ' where ' + @OrderfldName + ' ' + @strTmp + ' ( ' + replace ( @OrderfldName , @tblName1 , ' tblTmp ' ) + ' ) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' ' + @OrderfldName + ' from ' + @tblName2 + ' ' + @strOrder + ' ) as tblTmp) ' + @strOrder if @strWhere != '' set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @fldName + ' from ' + @tblName2 + ' where ' + @OrderfldName + ' ' + @strTmp + ' ( ' + replace ( @OrderfldName , @tblName1 , ' tblTmp ' ) + ' ) from (select top ' + str (( @PageIndex - 1 ) * @PageSize ) + ' ' + @OrderfldName + ' from ' + @tblName2 + ' where ' + @strWhere + ' ' + @strOrder + ' ) as tblTmp) ' + @strOrder if @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = ' select top ' + str ( @PageSize ) + ' ' + @fldName + ' from ' + @tblName2 + ' ' + @strTmp + ' ' + @strOrder end if @IsReCount != 0 set @strSQL = @strSQL + ' /*----*/ ' + ' select count(1) as Total from ' + @tblName2 + ' ' if @IsPrint <> 0 print ( @strSQL ) exec ( @strSQL ) GO
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
