原文:
SQL點滴16—SQL分頁語句總結
今天對分頁語句做一個簡單的總結，他們大同小異的，只要理解其中一個其他的就很好理解了。
使用top選項
select
top
10
*
from
Orders
a
where
a.orderid
not
in
(
select
top
10
orderid
from
Orders
order
by
orderid)
order
by
a.orderid
使用max函數
這種方法的前提是有唯一值的一個列。
select
top
10
*
from
Orders a
?
where
a.orderid
>
(
select
MAX
(orderid)
from
(
select
top
10
orderid
from
Orders
order
by
orderid)
as
orderid)
?
order
by
orderid
使用row_number()
select
*
from
(
select
ROW_NUMBER()
over
(
order
by
orderid)
as
rownumber,
*
from
Orders) myresult
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
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設置
begin
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
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
使用臨時表
begin
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
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
使用動態存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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
