分頁很重要，面試會遇到。不妨再回顧總結(jié)一下。
1.創(chuàng)建測試環(huán)境，(插入100萬條數(shù)據(jù)大概耗時5分鐘)。
create
database
DBTest
use
DBTest
--
創(chuàng)建測試表
create
table
pagetest
(
id
int
identity
(
1
,
1
)
not
null
,
col01
int
null
,
col02
nvarchar
(
50
)
null
,
col03
datetime
null
)
--
1萬記錄集
declare
@i
int
set
@i
=
0
while
(
@i
<
10000
)
begin
insert
into
pagetest
select
cast
(
floor
(
rand
()
*
10000
)
as
int
),
left
(
newid
(),
10
),
getdate
()
set
@i
=
@i
+
1
end
2.幾種典型的分頁sql，下面例子是每頁50條，198*50=9900，取第199頁數(shù)據(jù)。
--
寫法1，not in/top
select
top
50
*
from
pagetest
where
id
not
in
(
select
top
9900
id
from
pagetest
order
by
id)
order
by
id
--
寫法2，not exists
select
top
50
*
from
pagetest
where
not
exists
(
select
1
from
(
select
top
9900
id
from
pagetest
order
by
id)a
where
a.id
=
pagetest.id)
order
by
id
--
寫法3，max/top
select
top
50
*
from
pagetest
where
id
>
(
select
max
(id)
from
(
select
top
9900
id
from
pagetest
order
by
id)a)
order
by
id
--
寫法4，row_number()
select
top
50
*
from
(
select
row_number()
over
(
order
by
id)rownumber,
*
from
pagetest)a
where
rownumber
>
9900
select
*
from
(
select
row_number()
over
(
order
by
id)rownumber,
*
from
pagetest)a
where
rownumber
>
9900
and
rownumber
<
9951
select
*
from
(
select
row_number()
over
(
order
by
id)rownumber,
*
from
pagetest)a
where
rownumber
between
9901
and
9950
--
寫法5，在csdn上一帖子看到的，row_number() 變體，不基于已有字段產(chǎn)生記錄序號，先按條件篩選以及排好序，再在結(jié)果集上給一常量列用于產(chǎn)生記錄序號
select
*
from
(
select
row_number()
over
(
order
by
tempColumn)rownumber,
*
from
(
select
top
9950
tempColumn
=
0
,
*
from
pagetest
where
1
=
1
order
by
id)a
)b
where
rownumber
>
9900
3.分別在1萬，10萬(取1990頁)，100(取19900頁）記錄集下測試。
測試sql:
declare
@begin_date
datetime
declare
@end_date
datetime
select
@begin_date
=
getdate
()
<
.....YOUR CODE.....
>
select
@end_date
=
getdate
()
select
datediff
(ms,
@begin_date
,
@end_date
)
as
'
毫秒
'
?
1萬：基本感覺不到差異。
10萬：
100萬：
?
4.結(jié)論：
1.max/top,ROW_NUMBER()都是比較不錯的分頁方法。相比ROW_NUMBER()只支持sql2005及以上版本，max/top有更好的可移植性，能同時適用于sql2000,access。
2.not exists感覺是要比not in效率高一點(diǎn)點(diǎn)。
3.ROW_NUMBER()的3種不同寫法效率看起來差不多。
4.ROW_NUMBER() 的變體基于我這個測試效率實(shí)在不好。原帖在這里? http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html
?
PS.上面的分頁排序都是基于自增字段id。測試環(huán)境還提供了int,nvarchar,datetime類型字段，也可以試試。不過對于非主鍵沒索引的大數(shù)據(jù)量排序效率應(yīng)該是很不理想的。
?
5.簡單將ROWNUMBER,max/top的方式封裝到存儲過程。
ROWNUMBER():
create
proc
[
dbo
]
.
[
spSqlPageByRownumber
]
@tbName
varchar
(
255
),
--
表名
@tbFields
varchar
(
1000
),
--
返回字段
@PageSize
int
,
--
頁尺寸
@PageIndex
int
,
--
頁碼
@strWhere
varchar
(
1000
),
--
查詢條件
@StrOrder
varchar
(
255
),
--
排序條件
@Total
int
output
--
返回總記錄數(shù)
as
declare
@strSql
varchar
(
5000
)
--
主語句
declare
@strSqlCount
nvarchar
(
500
)
--
查詢記錄總數(shù)主語句
--
------------總記錄數(shù)---------------
if
@strWhere
!=
''
begin
set
@strSqlCount
=
'
Select @TotalCout=count(*) from
'
+
@tbName
+
'
where
'
+
@strWhere
end
else
begin
set
@strSqlCount
=
'
Select @TotalCout=count(*) from
'
+
@tbName
end
--
------------分頁------------
if
@PageIndex
<=
0
begin
set
@PageIndex
=
1
end
set
@strSql
=
'
Select * from (Select row_number() over(
'
+
@strOrder
+
'
) rowId,
'
+
@tbFields
+
'
from
'
+
@tbName
+
'
where 1=1
'
+
@strWhere
+
'
) tb where tb.rowId >
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
and tb.rowId <=
'
+
str
(
@PageIndex
*
@PageSize
)
exec
sp_executesql
@strSqlCount
,N
'
@TotalCout int output
'
,
@Total
output
exec
(
@strSql
)
Max/top:(簡單寫了下，需要滿足主鍵字段名稱就是"id")
create
proc
[
dbo
]
.
[
spSqlPageByMaxTop
]
@tbName
varchar
(
255
),
--
表名
@tbFields
varchar
(
1000
),
--
返回字段
@PageSize
int
,
--
頁尺寸
@PageIndex
int
,
--
頁碼
@strWhere
varchar
(
1000
),
--
查詢條件
@StrOrder
varchar
(
255
),
--
排序條件
@Total
int
output
--
返回總記錄數(shù)
as
declare
@strSql
varchar
(
5000
)
--
主語句
declare
@strSqlCount
nvarchar
(
500
)
--
查詢記錄總數(shù)主語句
--
------------總記錄數(shù)---------------
if
@strWhere
!=
''
begin
set
@strSqlCount
=
'
Select @TotalCout=count(*) from
'
+
@tbName
+
'
where
'
+
@strWhere
end
else
begin
set
@strSqlCount
=
'
Select @TotalCout=count(*) from
'
+
@tbName
end
--
------------分頁------------
if
@PageIndex
<=
0
begin
set
@PageIndex
=
1
end
set
@strSql
=
'
select top
'
+
str
(
@PageSize
)
+
'
* from
'
+
@tbName
+
'
where id>(select max(id) from (select top
'
+
str
((
@PageIndex
-
1
)
*
@PageSize
)
+
'
id from
'
+
@tbName
+
'
'
+
@strOrder
+
'
)a)
'
+
@strOrder
+
''
exec
sp_executesql
@strSqlCount
,N
'
@TotalCout int output
'
,
@Total
output
exec
(
@strSql
)
園子里搜到Max/top這么一個版本,看起來很強(qiáng)大， http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.html
調(diào)用：
declare
@count
int
--
exec [dbo].[spSqlPageByRownumber]'pagetest','*',50,20,'','order by id asc',@count output
exec
[
dbo
]
.
[
spSqlPageByMaxTop
]'
pagetest
'
,
'
*
'
,
50
,
20
,
''
,
'
order by id asc
'
,
@count
output
select
@count
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
