SQL點滴16—SQL分頁語句總結

今天對分頁語句做一個簡單的總結，他們大同小異的，隻要了解其中一個其他的就很好了解了。

　　

使用top選項

selecttop10*from Orders

a where a.orderid notin(selecttop10 orderid from Orders orderby orderid) orderby a.orderid

使用max函數

這種方法的前提是有唯一值的一個列。

selecttop10*from Orders a

 where a.orderid>(selectMAX(orderid) from (selecttop10 orderid from Orders orderby orderid) as orderid)

 orderby orderid

使用row_number()

select*from (select ROW_NUMBER() over(orderby orderid) as rownumber,*from Orders) myresult

where rownumber between10and20

selecttop10*from (select ROW_NUMBER() over(orderby orderid) as rownumber,*from Orders) myresult

where rownumber>10

with OrderedResult as(

select*,ROW_NUMBER() over(Orderby orderid) as rownumber from Orders)

select*from OrderedResult where rownumber between10and20

　　　　

使用rowcount設定

begin

declare@first_idvarchar(18),@startrowint

setrowcount10

select@first_id=orderid from Orders orderby orderid

select*from Orders where orderid>@first_idorderby orderid

setrowcount0

end

使用臨時表

declare@pagelowerboundint

declare@pageupperboundint

set@pagelowerbound=10

set@pageupperbound=20

createtable #pageindex([indexid]intidentity(1,1) notnull,[id]varchar(18))

declare@sqlnvarchar(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 innerjoin #pageindex b on a.orderid=b.id where b.indexid>@pagelowerboundorderby b.indexid

droptable #pageindex

使用動态存SQL語句

一個分頁存儲過程，支援多表連接配接的情況。原理還是使用主鍵。

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
--用途：分頁存儲過程(對有主鍵的表效率極高) 
ALTER PROCEDURE [dbo].[UP_GetRecordByPageIndex]
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主鍵字段名
    @PageSize     int = 10,           -- 頁尺寸
    @PageIndex    int = 1,            -- 頁碼
    @IsReCount    bit = 0,            -- 傳回記錄總數, 非 0 值則傳回
    @OrderType    bit = 0,            -- 設定排序類型, 非 0 值則降序
    @strWhere     varchar(1000) = '' -- 查詢條件 (注意: 不要加 where)
AS
declare @strSQL   varchar(6000)       -- 主語句
declare @strTmp   varchar(100)        -- 臨時變量(查詢條件過長時可能會出錯，可修改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



--用途：支援任意排序的分頁存儲過程 
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), -- 排序字段名,隻能與一個排序字段名School.ID 
@PageSize int = 10,   -- 頁尺寸 
@PageIndex int = 1,   -- 頁碼 
@IsReCount bit = 1,  -- 傳回記錄總數, 非 0 值則傳回 
@OrderType bit = 0,   -- 設定排序類型, 非 0 值則降序 
@strWhere varchar(1000)='', -- 查詢條件 (注意: 不要加 where) 'School.SchoolName like ''%浙江%'''
@IsPrint bit=0       --是否列印
AS

declare @strSQL varchar(6000) -- 主語句 
declare @strTmp varchar(1000)   -- 臨時變量(查詢條件過長時可能會出錯，可修改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      

作者：

Tyler Ning

出處：

http://www.cnblogs.com/tylerdonet/

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，如有問題，可以通過以下郵箱位址

[email protected]

 聯系我，非常感謝。