网上有很多人在写分页的sql,评论里面经常有人说,这个分页不好,那个过时了,我觉得这些好与不好,过时与不过时都需要根据具体情境来判断。今天我闲来无事写了一个使用top分页,数据只有1000条,虽然说明不了很多问题,但是作为自己学习的一个记录吧。
表结构如下:
USE [MyDB]
GO
/****** Object: Table [dbo].[MyTable] Script Date: 03/01/2015 21:18:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[Id] [int] NOT NULL,
[Info1] [nvarchar](50) NULL,
[Info2] [nvarchar](50) NULL,
[Info3] [nvarchar](50) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
先针对这张表写了分页语句:
--page index
declare @index int;
--row count
declare @count int;
--sql
declare @sql nvarchar(max);
set @index = 2;
set @count = 10;
set @sql = 'select top ' + CONVERT(nvarchar(20),@count) + ' * from MyTable where Id not in'+
'(select top '+ CONVERT(nvarchar(20),@count*(@index-1)) +' id from MyTable order by Id)'+
'order by Id';
print @sql;
exec(@sql);
得出的数据如下:
这时候把这个分页语句抽提成存储
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE prc_divpage2
@table nvarchar(50),
@fields nvarchar(50),
@wheres nvarchar(50),
@orders nvarchar(50),
@index int,
@count int
AS
BEGIN
--sql
declare @sql nvarchar(max);
set @sql = 'select top ' + CONVERT(nvarchar(20),@count) +' '+ @fields +' from '+ @table +' where Id not in'+
'(select top '+ CONVERT(nvarchar(20),@count*(@index-1)) +' id from ' + @table +' where '+ @wheres +
' order by '+ @orders +')'+
'order by '+ @orders;
print @sql;
exec(@sql);
END
GO
测试下:
总结:
这个使用top分页的存储过程可以指定表明,字段名,查询条件,排序字段等等,比较通用,但表中必须要有Id字段,而且只能做单表的分页,这个有待改进。