2859人阅读 评论(0)

# 2.常用的数据分页方法

## 方法1 定位法 (利用ID大于多少)

select top 10 * from tbl_FlightsDetail where FlightsDetailID>(
select max(FlightsDetailID) from (
select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID
) as t
) order by FlightsDetailID

## 方法2 (利用Not In)

select top 10* from tbl_FlightsDetail where FlightsDetailID not in (
select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID
) order by FlightsDetailID

## 方法3 (利用颠颠倒倒top)

select top 10* from (
select top 3000010* from tbl_FlightsDetail order by FlightsDetailID
) as t  order by t.FlightsDetailID desc

## 方法4 (ROW_NUMBER()函数)

select * from (
select *,ROW_NUMBER() OVER (ORDER BY FlightsDetailID) as rank from tbl_FlightsDetail
)  as t where t.rank between 3000001 and 3000010

执行计划：

Sql 2005版本或以上支持，也没用到索引，耗时2秒，速度还不错。

## 方法5 (利用IN)

select top 10 * from tbl_FlightsDetail  where FlightsDetailID in(
select top 10 FlightsDetailID from(
select top 3000010 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID
) as t order by t.FlightsDetailID desc
) order by FlightsDetailID

# 3.千万级分页存储过程

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--分页存储过程
CREATE PROCEDURE [dbo].[sp_Paging]
(
@Tables nvarchar(1000),                --表名/视图名
@PrimaryKey nvarchar(100),             --主键
@Sort nvarchar(200) = NULL,            --排序字段(不带order by)
@pageindex int = 1,                    --当前页码
@PageSize int = 10,                    --每页记录数
@Fields nvarchar(1000) = N'*',         --输出字段
@Filter nvarchar(1000) = NULL,         --where过滤条件(不带where)
@Group nvarchar(1000) = NULL,          --Group语句(不带Group By)
@TotalCount int OUTPUT                 --总记录数
)
AS

DECLARE @SortTable nvarchar(100)
DECLARE @SortName nvarchar(100)
DECLARE @strSortColumn nvarchar(200)
DECLARE @operator char(2)
DECLARE @type nvarchar(100)
DECLARE @prec int

--设定排序语句
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END

--设置排序字段类型和精度
SELECT @type=t.name, @prec=c.prec FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize nvarchar(50)
DECLARE @strStartRow nvarchar(50)
DECLARE @strFilter nvarchar(1000)
DECLARE @strSimpleFilter nvarchar(1000)
DECLARE @strGroup nvarchar(1000)

IF @pageindex <1
SET @pageindex = 1
SET @strPageSize = CAST(@PageSize AS nvarchar(50))
--设置开始分页记录数
SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))
--筛选以及分组语句
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY '
--计算总记录数
DECLARE @TotalCountSql nvarchar(1000)
SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilter
EXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT
--执行查询语句
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)

现在我们来测试一下：

DECLARE    @return_value int,
@TotalCount int

EXEC    @return_value = [dbo].[sp_Paging]
@Tables = N'tbl_FlightsDetail',
@PrimaryKey = N'FlightsDetailID',
@Sort = N'FlightsDetailID',
@pageindex = 299999,
@PageSize = 10,
@Fields = '*',
@Filter = NULL,
@Group = NULL,
@TotalCount = @TotalCount OUTPUT

SELECT    @TotalCount as N'@TotalCount'

SELECT    'Return Value' = @return_value

DECLARE @SortColumn varchar(40)
--即 top 3000001，取出最大的 id覆盖@SortColumn
SET ROWCOUNT  3000001
SELECT @SortColumn= FlightsDetailID  FROM tbl_FlightsDetail ORDER BY FlightsDetailID
--即 top 10
SET ROWCOUNT 10
SELECT *  FROM  tbl_FlightsDetail  WHERE FlightsDetailID   >= @SortColumn  ORDER BY  FlightsDetailID   

# 4.小结

0
0

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：891792次
• 积分：9838
• 等级：
• 排名：第1840名
• 原创：110篇
• 转载：58篇
• 译文：8篇
• 评论：408条
阅读排行
评论排行
最新评论