二、以下示例將返回行號為 50 到 60（含）的行，并以 OrderDate 排序。
USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
三、實現分頁存儲過程(固定到某個表的數據)
?
SET?ANSI_NULLS?ON
GO
SET?QUOTED_IDENTIFIER?ON
GO
-- =============================================
-- Author:????? cxw
-- Create date: 2006/11/14
-- Description:?利用Row_Number函數實現分頁存儲過程
?
-- exec DividePageForROW_NUMBER 2,1,'',''
?
-- =============================================
Create?PROCEDURE?DividePageForROW_NUMBER
?
@iPageSize??????int?????????--每頁數
,@iIndexPage????int?????????--第幾頁
,@vOrderBy??????varchar(50)?--排序字段
,@iRowCount?????int?????????--總記錄數
?
AS
BEGIN
SET?NOCOUNT?ON;
with?PageTable?as
(
select?row_number()?over(order?by?ReasonID)?TID,*?from?A
)
select?*
from?PageTable
where?TID?between?((@iIndexPage-1)*@iPageSize+1)?and?@iPageSize*@iIndexPage
?
SET?NOCOUNT?OFF;
?
END
GO
?
?
?
use study;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:??cxw
-- Create date: 2006/11/14
-- Description:?利用Row_Number函數實現分頁存儲過程(輸出總記錄數用返回一個字段)
--select * from OrderDetail
/*
--table表的分頁
exec [PublicPageQuery] @vQueryString='OrderDetail'
,@vKeyID='OrderNo'
,@vQueryType='table'
--為空就取表里所有字段,否則取自已設定字段， 最好不要取所有，指定
,@vColunmString='OrderNo,ProdNo,ProdName'?
,@vIsHaveRowCount='no'
,@iPageSize=5
,@iIndexPage=2
--查詢字符串的分頁(單一的查詢條件)
exec [PublicPageQuery] @vQueryString='select OrderNo,ProdNo,ProdName?
from? OrderDetail?
where OrderNo like ''%So%''
and ProdNo like ''%%'''
,@vKeyID='OrderNo'
,@vQueryType='select'
,@vColunmString='OrderNo,ProdNo,ProdName'?
,@vIsHaveRowCount='no'
,@iPageSize=5
,@iIndexPage=2
--帶有關聯的查詢字符串
exec [PublicPageQuery] @vQueryString='SELECT *?
?FROM(
??SELECT * FROM (
??SELECT RequestNo,RequestTypeID,RequestType,RequestUser,NeedToAddress,NeedArriveTime,Memo,Status
??FROM ERPDATA.dbo.TP_RequestOfficeCar
??UNION
??SELECT A.RequestNo,A.RequestTypeID,RequestType,RequestUser,B.CustomerAddress as NeedToAddress,B.NeedArriveTime,A.Memo,A.Status
??FROM ERPDATA.dbo.TP_RequestDeliverGoodsCar A,ERPDATA.dbo.TP_RequestDeliverGoodsCarDetail B
??WHERE A.RequestNo=B.RequestNo
??) M
??WHERE? isnull(RequestUser,'') LIKE ''%%''
???AND isnull(NeedToAddress,'') LIKE ''%%''
???AND isnull(Status,'') like ''%%''
???AND isnull(Memo,'') LIKE ''%%''?) Main? '
,@vKeyID='OM.OrderNo'
,@vQueryType='select'
,@vColunmString='OrderNo,BillTo,BillToName'
,@vIsHaveRowCount='no'
,@iPageSize=5
,@iIndexPage=1
*/
-- =============================================
Create?PROCEDURE [dbo].[PublicPageQuery]
@vQueryString??nvarchar(MAX)??--查詢字符串,或表名
,@vKeyID???varchar(200)??--關鍵字(排序字段,索引字段,最好用索引字段,大大提高分頁查詢速度)
,@vQueryType??varchar(20)???--查詢類型，為表(table)，查詢語句(select)
,@vColunmString??nvarchar(2000)??--所顯示字段值(最好都添上此字段名)
,@vIsHaveRowCount?varchar(5)???--是否顯示總記錄數,有(yes),不(no)
,@iPageSize??int??????--每頁數
,@iIndexPage?int??????--第幾頁
AS
BEGIN
SET NOCOUNT ON;
declare @vSQLString varchar(8000)?--查詢SQL字符串
,@vRowCountString varchar(50)??--自動增量統計查詢字符串
,@vRowNumberString varchar(200)??--自動增量查詢字符串
,@vWhereKeyStr varchar(10)???--條件關鍵字
,@iStart int??????--開始位置
,@iEnd int???????--結束位置
--將SQL語句優化,去掉沒有必要的查詢條件
--select * from? dbo.RecurrentSplit(@vQueryString,'and ',1,1)
--where isnull(SplitName,'')<>''
--return
set @vWhereKeyStr='%%'??--所要去掉的關鍵查詢條件
if(charindex(@vWhereKeyStr,@vQueryString)=0)
begin
?goto lbl_Query
end
set @vQueryString=replace(@vQueryString,'
',' ')?--去掉回車符
select @vQueryString=case when? A.IndexNo>0 --最少保留第一條查詢條件，第一個條件最好最經常查詢字段條件
?then?
???case when charindex(@vWhereKeyStr,B.SplitName)>0?
???then?
????--只替換所需要修改的無條件的字符串。
????replace(@vQueryString
?????,substring(B.SplitName,0,charindex(@vWhereKeyStr,B.SplitName)+len(@vWhereKeyStr)+2),'')?
???else @vQueryString?
??end
?else
??@vQueryString
?end
--select @vQueryString=
--??case when charindex('%%',B.SplitName)>0?
--??then replace(@vQueryString,B.SplitName,'')?
--??else @vQueryString?
--??end
from?
(
select * from? dbo.RecurrentSplit(@vQueryString,'and ',1,1)--字符串分割函數，之前有
where isnull(SplitName,'')<>''
) A
cross apply dbo.RecurrentSplit(A.SplitName,'or ',1,1) B
--set @vQueryString=case when charindex('''',@vQueryString,charindex('where',@vQueryString))>0
--then @vQueryString else left(@vQueryString,charindex('where',@vQueryString)-1) end
--select @vQueryString
--return
--查詢斷點
lbl_Query:
select @vSQLString=''
,@vRowNumberString='PageIndex=row_number() over(order by?'+@vKeyID+')'
,@vQueryString=ltrim(rtrim(@vQueryString))
,@vColunmString=case when @vQueryType='table'
?then
??case when @vColunmString=''?
??then '*'
??else @vColunmString?
??end
?else --取出所要取出的字段
??case when @vColunmString=''
??then
???substring(@vQueryString,len('select')+1,charindex('from',@vQueryString)-len('select')-1)
??else @vColunmString end
?end
,@vQueryString=case? @vQueryType when 'table'?
?then 'select?'+@vRowNumberString+char(13)
??+','+@vColunmString+' from?'+@vQueryString?
?when 'select'?
?then 'select?'+@vRowNumberString+char(13)
?+','+right(@vQueryString,len(@vQueryString)-len('select'))--'('+@vQueryString+')Alias'?
?else 'procedure'
?end
,@vRowCountString=case when @vIsHaveRowCount='yes'?
?then ',(select count(0) from PageTable) ''RowCount'''
?else '' end
,@iStart=(@iIndexPage-1)*@iPageSize+1
,@iEnd=@iPageSize*@iIndexPage
set @vSQLString='with PageTable as
(
'+@vQueryString+'
)
select '
+@vColunmString+char(13)
+case when @vRowCountString='' then '' else @vRowCountString+char(13) end
+'from PageTable
where PageIndex between '+cast(@iStart as varchar)+' and '+cast(@iEnd as varchar)
print @vSQLString
exec(@vSQLString)
SET NOCOUNT OFF;
END
