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

使用臨時表

begin
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
end

使用動態存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

posted @ 2011-06-01 15:10 nd 閱讀(1568) 評論(4) 收藏舉報

刷新頁面返回頂部

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

公告