先看下表中共有多少条数据：
一百二十多万条，呵呵。
sql语句：
declare
@d
datetime
set
@d
=
getdate
()
select
top
10
ID,U_Name,U_Age,U_Sex,U_Address
from
Test1
where
ID
not
in
(
select
top
9990
ID
from
Test1
order
by
ID)
order
by
ID
select
[
not in方法升序分页执行花费时间(毫秒)
]
=
datediff
(ms,
@d
,
getdate
())
declare
@s
datetime
set
@s
=
getdate
()
select
top
10
ID,U_Name,U_Age,U_Sex,U_Address
from
Test1
where
ID
>=
(
select
max
(ID)
from
(
select
top
9991
ID
from
test1
order
by
ID)
as
T)
order
by
ID
select
[
Max方法升序分页执行花费时间(毫秒)
]
=
datediff
(ms,
@s
,
getdate
())
declare
@t
datetime
set
@t
=
getdate
()
select
top
10
ID,U_Name,U_Age,U_Sex,U_Address
from
Test1
where
ID
not
in
(
select
top
9990
ID
from
Test1
order
by
ID
desc
)
order
by
ID
desc
select
[
not in方法降序分页执行花费时间(毫秒)
]
=
datediff
(ms,
@t
,
getdate
())
declare
@q
datetime
set
@q
=
getdate
()
select
top
10
ID,U_Name,U_Age,U_Sex,U_Address
from
Test1
where
ID
<=
(
select
min
(ID)
from
(
select
top
9991
ID
from
Test1
order
by
ID
desc
)
as
T)
order
by
ID
desc
select
[
Min方法降序分页执行花费时间(毫秒)
]
=
datediff
(ms,
@q
,
getdate
())
分页效率一目了然！
两种方法的不同点之一就是计算第二个top中的值
not in 中计算公式为：
pgSize
*
(pgNo
-
1
)
max\min中计算公式为：
((pgNo
-
1
)
*
pgSize
+
1
)
pgNo为当前第几页，pgSize为每页几条数据。
