原文:
SQL點(diǎn)滴16—SQL分頁(yè)語(yǔ)句總結(jié)
今天對(duì)分頁(yè)語(yǔ)句做一個(gè)簡(jiǎn)單的總結(jié)，他們大同小異的，只要理解其中一個(gè)其他的就很好理解了。
使用top選項(xiàng)
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函數(shù)
這種方法的前提是有唯一值的一個(gè)列。
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設(shè)置
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
使用臨時(shí)表
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
使用動(dòng)態(tài)存SQL語(yǔ)句
一個(gè)分頁(yè)存儲(chǔ)過(guò)程，支持多表連接的情況。原理還是使用主鍵。
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
--
用途：分頁(yè)存儲(chǔ)過(guò)程(對(duì)有主鍵的表效率極高)
ALTER
PROCEDURE
[
dbo
]
.
[
UP_GetRecordByPageIndex
]
@tblName
varchar
(
255
),
--
表名
@fldName
varchar
(
255
),
--
主鍵字段名
@PageSize
int
=
10
,
--
頁(yè)尺寸
@PageIndex
int
=
1
,
--
頁(yè)碼
@IsReCount
bit
=
0
,
--
返回記錄總數(shù), 非 0 值則返回
@OrderType
bit
=
0
,
--
設(shè)置排序類(lèi)型, 非 0 值則降序
@strWhere
varchar
(
1000
)
=
''
--
查詢條件 (注意: 不要加 where)
AS
declare
@strSQL
varchar
(
6000
)
--
主語(yǔ)句
declare
@strTmp
varchar
(
100
)
--
臨時(shí)變量(查詢條件過(guò)長(zhǎng)時(shí)可能會(huì)出錯(cuò)，可修改100為1000)
declare
@strOrder
varchar
(
400
)
--
排序類(lèi)型
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
--
用途：支持任意排序的分頁(yè)存儲(chǔ)過(guò)程
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
,
--
頁(yè)尺寸
@PageIndex
int
=
1
,
--
頁(yè)碼
@IsReCount
bit
=
1
,
--
返回記錄總數(shù), 非 0 值則返回
@OrderType
bit
=
0
,
--
設(shè)置排序類(lèi)型, 非 0 值則降序
@strWhere
varchar
(
1000
)
=
''
,
--
查詢條件 (注意: 不要加 where) 'School.SchoolName like ''%浙江%'''
@IsPrint
bit
=
0
--
是否打印
AS
declare
@strSQL
varchar
(
6000
)
--
主語(yǔ)句
declare
@strTmp
varchar
(
1000
)
--
臨時(shí)變量(查詢條件過(guò)長(zhǎng)時(shí)可能會(huì)出錯(cuò)，可修改100為1000)
declare
@strOrder
varchar
(
400
)
--
排序類(lèi)型
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號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
