原文:
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
