sql T_sql 巧用with做自定义数据表分页方法

1。依旧是先创建数据库表，并插入相关数据，如果在此之前已经有该表的话，这里就不需要创建了。

CREATE TABLE [dbo].[StudentsScore](  

    [Student] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,  

    [Subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,  

    [Score] [int] NULL 

) ON [PRIMARY]  

 

GO  

SET ANSI_PADDING OFF  

--插入数据记录脚本：

Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '中文', 80 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '数学', 78 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '英语', 92 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '中文', 89 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '数学', 87 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '英语', 75 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '中文', 92 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '数学', 74 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '英语', 65 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '中文', 79 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '数学', 83 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '英语', 81 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '中文', 73 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '数学', 84 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '英语', 93 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '中文', 79 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '数学', 86 );   

Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '英语', 84 );

 

2。先来说说with的用法，请看下面的例子：

/*with的基本用法*/

with cust as

(

    select * from dbo.StudentsScore where score>=80

)

 

select * from cust

 

/*运行后返回的是score>=80的数据，如下图：*/

/*

从上面的例子，我们可以发现到，with其实很类似于临时表的作用，这里只是对with的简单应用，

还有相关的功能，请参考sql2005帮助文档的相关介绍。

*/

-----------------------------------------------------------------------

 

/*

接下来是with加over的用法，over可以用来做自增序列号（虚拟的），行号的唯一标识。 

*/

/*with加over用法*/

with cust as

(

    select row_number() over (order by student) as 序号,* from dbo.StudentsScore

)

 

select * from cust

/*运行后效果如下图，我们发现，它自动产生了一列像自增行号的列，这也是我们做分页的依据*/

 

 

 

3。接下来是分页的存储过程实现：

 

/*我自己做的分页新例子*/

 

alter proc proc_SplitPages

(

     @tb varchar(1000),--表名

     @tbSoftField varchar(100),--排序的字段

     @pagesize int,--页面条数

     @pagecount int--第几页

)

as

begin

declare @start int

declare @end int

 

set @start = (@pagecount-1)*@pagesize+1;

set @end = @pagecount*@pagesize;

 

declare @sql varchar(max)

set @sql=''

set @sql='

       with cust as

       (

           select row_number() over (order by '+@tbSoftField+') as rownumber, *  from '+@tb+'

       )

       select * from cust where rownumber  between '''+cast(@start as varchar(100))+''' and '''+cast(@end as varchar(100))+'''

       '

exec (@sql)

end

 

/*

下面是每页10条数据的第1页

exec proc_SplitPages 'StudentsScore','student',10,1

效果如图：

 

 

*/

 

备注：这里的分页是动态的，可以接收任意数据表，对其进行分页，也就是说，我们无需再对

别的数据表做专门的分页功能，直接可以运用此存储过程，即可以达到分页的效果。

下面是每页10条数据的第2页

exec proc_SplitPages 'StudentsScore','student',10,2

效果如图：