sql分页脚本，可在java中使用

最新推荐文章于 2018-05-20 13:01:30 发布

置顶 u011097980

最新推荐文章于 2018-05-20 13:01:30 发布

阅读量769

点赞数

分类专栏： SQL 文章标签： sql存储过程分页

本文链接：https://blog.csdn.net/u011097980/article/details/9453597

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

-----1.简单的分页

USE [j1216]
GO
/****** Object:  StoredProcedure [dbo].[pageinaction]    Script Date: 07/24/2013 17:05:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[pageinaction]
(@pageNum int,
@pageSize int,
@tableName varchar(30),
@uniqid varchar(20),
@pagetotal int out)

as
declare
   @sql nvarchar(200),
   @total int,
   @temppage int
begin
  --创建临时表 存储表中的总记录数
 -- set @sql='create table temp(total int)'
  --exec(@sql)
  --利用动态sql语句 获得总的记录数
  set @sql='select @con=count(*) from '+@tableName
  --将总的记录数，插入到临时表的count字段中
  --insert into temp exec(@sql)
--exec sp_executesql @sql,N'@con int output',@total output
print @total
--select @total=total from temp
  --取出临时表中的总记录数之后 删除临时表
  --set @sql='drop table temp'
  --exec(@sql)
  print '总记录数'+cast(@total as varchar(30))
  --获得总页数
  if @total%@pageSize=0
    set @pagetotal=@total/@pageSize
  else
  set @pagetotal=@total/@pageSize+1
  print '总页数'+cast(@pagetotal as varchar(30))
  --当用户输入的页码小于1的时候 将用户的输入页置为1  
  if @pageNum<1
  set @pageNum=1
  --计算排除的记录数(前多少条数据)
  set @temppage=(@pageNum-1)*@pageSize
  --拼装获取数据的语句
  set @sql='select top '+cast(@pageSize as varchar(20))+' * from '+@tableName +' where '+@uniqid+' not in
  (select top '+cast(@temppage as varchar(20))+' '+@uniqid +' from '+@tableName+')'
  print '分页语句'+@sql
  --执行sql 获取结果集
 exec(@sql)
    
end

-------调用方式如下

declare @pagetotal int

exec exec pageinaction 当前页,每页显示的数据数，'表或试图','用于定位记录的主键(惟一键)字段,只能是单个字段',@pagetotal

-------牛逼分页

USE [tclmss]
GO
/****** Object:  StoredProcedure [dbo].[Sp_PapeView]    Script Date: 07/24/2013 17:29:51 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO



--调用示例exec Sp_PapeView 'M_USER_TEST_REC','TestNo',100,10,'','TestNo','',0,0
ALTER  PROC [dbo].[Sp_PapeView]
@TblName    sysname,            --要分页显示的表名
@FieldShow  nvarchar(1000)='',  --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldKey  sysname,            --用于定位记录的主键(惟一键)字段,只能是单个字段
@FieldOrder  nvarchar(1000)='',  --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
@PageSize  int=10,            --每页的大小(记录数)
@PageCurrent int=1,              --要显示的页码
@RecordCount  int OUTPUT, --总记录数
@PageCount  int OUTPUT,        --总页数  
@FiledWhere    nvarchar(3000)=''  --查询条件

AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@TblName) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@TblName)
RETURN
END

IF OBJECTPROPERTY(OBJECT_ID(@TblName),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@TblName),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@TblName),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@TblName)
RETURN
END


--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END


--其他参数检查及规范
IF ISNULL(@PageCurrent,0) <1 SET @PageCurrent=1
IF ISNULL(@PageSize,0) <1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@FiledWhere,N'')=N''
SET @FiledWhere=N''
ELSE
SET @FiledWhere=N'WHERE ('+@FiledWhere+N')'


--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @RecordCount=COUNT(*)'
  +N' FROM '+@TblName
  +N' '+@FiledWhere
EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
SET @PageCount=(@RecordCount+@PageSize-1)/@PageSize
END
 


--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize


--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
  +N' '+@FieldShow
  +N' FROM '+@TblName
  +N' '+@FiledWhere
  +N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
  @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n <'+@TopN
  +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
  +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@TblName
  +N' '+@FiledWhere
  +N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
  N'@n int,@s nvarchar(4000) OUTPUT',
  @PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
  EXEC(N'SELECT TOP 0'
  +N' '+@FieldShow
  +N' FROM '+@TblName)
ELSE
BEGIN
  SET @sql=STUFF(@sql,1,1,N'')  
  --执行查询
  EXEC(N'SELECT TOP '+@TopN
  +N' '+@FieldShow
  +N' FROM '+@TblName
  +N' WHERE '+@FieldKey
  +N' IN('+@sql
  +N') '+@FieldOrder)
END
END

------------------------------基于视图的分页

USE [zj2hjlms2013-07-30]
GO
/****** Object:  StoredProcedure [dbo].[getS_YCL_ALL_TONGJI]    Script Date: 08/03/2013 14:42:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[getS_YCL_ALL_TONGJI] (@currentPage varchar(8000),@perPage varchar(8000),
   @order varchar(8000),@queryDateStr varchar(8000),@queryStr varchar(8000)) AS
/*
名称：getPagingChargeOne
功能：查询（分页） ，带分页和自定义排序
* @param currentPage 当前页(注：如果为0则返回总条数)
* @param perPage 每页显示条数
* @param order 排序条件
* @param @queryDateStr 日期条件
* @param @queryStr 其它查询条件
* @param @PageCount  总页数
* @param @RecordCount  数据总条数
Author: sgy 2013.07.13 v1.0
*/

declare @resultQuery nvarchar(4000)
create table #cursor2(id nvarchar(800),
      bhgcount varchar(800),
      zigongsiid varchar(800),
      zigongsiparentid varchar(800),
      type varchar(800),
      totalcount varchar(800),
      ljjccount varchar(800),
      usergroupid varchar(800),
      usergroupname varchar(800),
      ycl2 varchar(800),
      ycl3 varchar(800),
      unitname varchar(800))

select @resultQuery='select * from (select g.id,(CASE WHEN (g.bhgcount IS NULL) OR(g.bhgcount = '''') THEN ''0'' ELSE g.bhgcount END) bhgcount,'+
    ' g.zigongsiid,g.zigongsiparentid,g.type,g.totalcount,g.ljjccount,g.usergroupid,g.ycl2,g.ycl3,'+
    ' h.usergroupname,h.unitname'+
     ' from ('+
    ' select e.id,(case when (f.bhgcount is null) or (f.bhgcount='''') then ''0'' else f.bhgcount end) as bhgcount,e.zigongsiid,e.zigongsiparentid,e.type,e.totalcount,e.ljjccount,e.usergroupid,e.ycl2,e.ycl3 from ('+

    ' (select max(id) as id,zigongsiparentid,zigongsiid, type,COUNT(type) bhgcount,a.usergroupid,ycl2,ycl3 from'+
    ' S_YCL_ALL a,X_UserGroup b '+
    ' where a.usergroupid=b.usergroupid and '+@queryDateStr+' and ycl12=''不合格'' group by zigongsiid,a.usergroupid,zigongsiparentid,type,ycl2,ycl3'+
    ' ) f'+

    ' right join'+

    ' (select max(a.id) as id,zigongsiparentid,zigongsiid, type,COUNT(type) totalcount,sum(cast((CASE WHEN (ycl6 IS NULL) or (ycl6 = '''') THEN ''0'' ELSE ycl6 END) as int)) as ljjccount,a.usergroupid,ycl2,ycl3 from '+
    ' S_YCL_ALL a,X_UserGroup b '+
    ' where a.usergroupid=b.usergroupid and '+@queryDateStr+' group by zigongsiid,a.usergroupid,zigongsiparentid,type,ycl2,ycl3'+
    ' ) e'+
    ' on f.usergroupid=e.usergroupid and f.type=e.type and f.ycl2=e.ycl2 and f.ycl3=e.ycl3'+

    ' )'+

    ' ) g ,X_UserGroup h where g.usergroupid=h.usergroupid) k where'+@queryStr
     
print @resultQuery
insert  into #cursor2 (id,
      bhgcount,
      zigongsiid,
      zigongsiparentid,
      type,
      totalcount,
      ljjccount,
      usergroupid,
      usergroupname,
      ycl2,
      ycl3,
      unitname) execute sp_executesql @resultQuery
      
      
--返回总条数
if @currentpage='0'
begin
   
 select @resultQuery='select count(*) as count from #cursor2'

 

end

else
--返回指定页数的数据
begin

create table #cursor1(id nvarchar(800))
declare @pastRecord nvarchar(4000)
select @pastRecord =' select top '+cast((@currentpage-1)*@perpage   as   varchar)+' id from '+
    '#cursor2 '+@order
print @pastRecord
insert  into #cursor1 (id) execute sp_executesql @pastRecord

select @resultQuery =' select top '+cast(@perpage   as   varchar)+' *  from '+
 '#cursor2 where id not in (select id from #cursor1 ) '+@order
 
end

execute sp_executesql @resultQuery