SQLSERVER分页查询ITeye - 凯发娱乐

SQLSERVER分页查询ITeye

2019年04月04日15时47分59秒 | 作者: 鸿云 | 标签: 查询,代码,计划 | 浏览: 418

Sqlserver数据库分页查询一直是Sqlserver的短板,闲来无事,想出几种办法,假设有表ARTICLE,字段ID、YEAR...(其他省掉),数据53210条(客户实在数据,量不大),分页查询每页30条,查询第1500页(即第45001-45030条数据),字段ID集合索引,YEAR无索引,Sqlserver版别:2008R2

第一种计划、最简略、一般的办法:

 

仿制代码 代码如下:
SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC

 

均匀查询100次所需时刻:45s

第二种计划:

 

仿制代码 代码如下:
SELECT * FROM ( SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

 

均匀查询100次所需时刻:138S

第三种计划:

 

仿制代码 代码如下:
SELECT * FROM ARTICLE w1,
(
SELECT TOP 30 ID FROM
(
SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

 

均匀查询100次所需时刻:21S

第四种计划:

仿制代码 代码如下:
SELECT * FROM ARTICLE w1
WHERE ID in
(
SELECT top 30 ID FROM
(
SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
)
ORDER BY w1.YEAR DESC, w1.ID DESC

 

均匀查询100次所需时刻:20S

第五种计划:

 

仿制代码 代码如下:
SELECT w2.n, w1.* FROM ARTICLE w1, ( SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2 WHERE w1.ID = w2.ID AND w2.n 50000 ORDER BY w2.n ASC

 

均匀查询100次所需时刻:15S

查询第1000-1030条记载

第一种计划:

 

仿制代码 代码如下:
SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 1000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC

 

均匀查询100次所需时刻:80s

第二种计划:

仿制代码 代码如下:
SELECT * FROM ( SELECT TOP 30 * FROM (SELECT TOP 1030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

 

均匀查询100次所需时刻:30S

第三种计划:

 

仿制代码 代码如下:
SELECT * FROM ARTICLE w1,
(
SELECT TOP 30 ID FROM
(
SELECT TOP 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

 

均匀查询100次所需时刻:12S

第四种计划:

 

仿制代码 代码如下:
SELECT * FROM ARTICLE w1
WHERE ID in
(
SELECT top 30 ID FROM
(
SELECT top 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
)
ORDER BY w1.YEAR DESC, w1.ID DESC

 

均匀查询100次所需时刻:13S

第五种计划:

仿制代码 代码如下:
SELECT w2.n, w1.* FROM ARTICLE w1,( SELECT TOP 1030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE) w2 WHERE w1.ID = w2.ID AND w2.n 1000 ORDER BY w2.n ASC

 

均匀查询100次所需时刻:14S

由此可见在查询页数靠前时,功率3 4 5 2 1,页码靠后时5 4 3 1 2,再依据用户习气,一般用户的检索只看最前面几页,因而挑选3 4 5计划均可,若归纳考虑计划5是最好的挑选,可是要注意SQL2000不支持row_number()函数,因为时刻和条件的约束没有做更深化、规模更广的测验,有爱好的能够细心研讨下。

以下是依据第四种计划编写的一个分页存储进程:

 

仿制代码 代码如下:
if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[sys_Page_v2]) and OBJECTPROPERTY(id, NIsProcedure) = 1)
drop procedure [dbo].[sys_Page_v2]
GO

 

CREATE PROCEDURE [dbo].[sys_Page_v2]
@PCount int output, 总页数输出
@RCount int output, 总记载数输出
@sys_Table nvarchar(100), 查询表名
@sys_Key varchar(50), 主键
@sys_Fields nvarchar(500), 查询字段
@sys_Where nvarchar(3000), 查询条件
@sys_Order nvarchar(100), 排序字段
@sys_Begin int, 开端方位
@sys_PageIndex int, 当时页数
@sys_PageSize int 页巨细
AS

SET NOCOUNT ON
SET ANSI_WARNINGS ON

IF @sys_PageSize 0 OR @sys_PageIndex 0
BEGIN
RETURN
END

DECLARE @new_where1 NVARCHAR(3000)
DECLARE @new_order1 NVARCHAR(100)
DECLARE @new_order2 NVARCHAR(100)
DECLARE @Sql NVARCHAR(4000)
DECLARE @SqlCount NVARCHAR(4000)

DECLARE @Top int

if(@sys_Begin =0)
set @sys_Begin=0
else
set @sys_Begin=@sys_Begin-1

IF ISNULL(@sys_Where,) =
SET @new_where1 =
ELSE
SET @new_where1 = WHERE + @sys_Where

IF ISNULL(@sys_Order,)
BEGIN
SET @new_order1 = ORDER BY + Replace(@sys_Order,desc,)
SET @new_order1 = Replace(@new_order1,asc,desc)

SET @new_order2 = ORDER BY + @sys_Order
END
ELSE
BEGIN
SET @new_order1 = ORDER BY ID DESC
SET @new_order2 = ORDER BY ID ASC
END

SET @SqlCount = SELECT @RCount=COUNT(1),@PCount=CEILING((COUNT(1)+0.0)/
+ CAST(@sys_PageSize AS NVARCHAR)+) FROM + @sys_Table + @new_where1

EXEC SP_EXECUTESQL @SqlCount,N@RCount INT OUTPUT,@PCount INT OUTPUT,
@RCount OUTPUT,@PCount OUTPUT

IF @sys_PageIndex CEILING((@RCount+0.0)/@sys_PageSize) 假如输入的当时页数大于实践总页数,则把实践总页数赋值给当时页数
BEGIN
SET @sys_PageIndex = CEILING((@RCount+0.0)/@sys_PageSize)
END

set @sql = select + @sys_fields + from + @sys_Table + w1
+ where + @sys_Key + in (
+select top + ltrim(str(@sys_PageSize)) + + @sys_Key + from
+(
+select top + ltrim(STR(@sys_PageSize * @sys_PageIndex + @sys_Begin)) + + @sys_Key + FROM
+ @sys_Table + @new_where1 + @new_order2
+) w + @new_order1
+) + @new_order2

print(@sql)

Exec(@sql)

GO

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章