基本的思路有如下几种:
- 从12.5.3版本开始sybase支持top关键字,试着使用,但是未果(同事说sybase对top支持不好),因为不支持嵌套。使用的是如下的sql:
select top 10 t1.AH, t1.BH from K_ZS..B_MS t1 where t1.BH not in ( select top 20 t2.BH from K_ZS..B_MS t2)这里的是不带order by的,另外一种是带order by的,效率会比较高。
- 在存储过程端使用set rowcount 来实现分页,没有试验。
- 用存储过程,建立临时表,获取数据,然后动态sql获得临时表数据。最后从网上找到的如下的两个分页存储过程:
-- 按行读取
CREATE PROCEDURE GetDataByLine
(
-- 创建一个分页读取过程
@SqlStr varchar ( 8000 ), -- SQL语句
@FirstRec int , -- 页起始行
@LastRec int -- 页结束行
)
AS
DECLARE @dt varchar ( 10 ) -- 生成临时表的随机数
BEGIN
SELECT @dt = substring ( convert ( varchar , rand ()), 3 , 10 ) -- 一个字符型的随机数
-- 将搜索结果放入临时表中,表名随机生成,在' FROM '前插入'INTO '+随机临时表名
SELECT @SqlStr = stuff ( @SqlStr , charindex ( ' FROM ' , upper ( @SqlStr )), 6 , ' INTO tempdb..Lining ' + @dt + ' FROM ' )
EXECUTE ( @SqlStr )
-- 为临时表增加id号
SELECT @SqlStr = ' ALTER TABLE tempdb..Lining ' + @dt + ' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY '
EXECUTE ( @SqlStr )
-- 计算临时表中的记录数
-- SELECT @SqlStr = 'SELECT Count(*) From tempdb..Lining' + @dt
-- EXECUTE (@SqlStr)
-- 选取记录号在起始行和结束行中间的记录
SELECT @SqlStr = ' SELECT * FROM tempdb..Lining ' + @dt + ' WHERE TEMPDB_ID > ' + convert ( varchar , @FirstRec ) + ' and TEMPDB_ID <= ' + convert ( varchar , @LastRec )
EXECUTE ( @SqlStr )
-- 删除临时表
SELECT @SqlStr = ' DROP TABLE tempdb..Lining ' + @dt
EXECUTE ( @SqlStr )
END
/*
some comments:
1.@SqlStr varchar(8000), depends on your page size
2. this is a generic paging sp, if you just want to use it for specific table,
you'd better change the 'tempdb..Lining' to #Paging, the performance will be better
*/
-- 按页读取
CREATE PROCEDURE GetDataByPage
(
-- 创建一个分页读取过程
@SqlStr varchar ( 8000 ), -- SQL语句
@PageSize int , -- 每页记录数
@CurrentPage int -- 当前页数
)
AS
DECLARE @FirstRec int , @LastRec int , @dt varchar ( 10 ) -- 页起始行,页结束行,生成临时表的随机数
BEGIN
SELECT @FirstRec = ( @CurrentPage - 1 ) * @PageSize -- 计算页起始行
SELECT @LastRec = ( @CurrentPage * @PageSize + 1 ) -- 计算页结束行
SELECT @dt = substring ( convert ( varchar , rand ()), 3 , 10 ) -- 一个字符型的随机数
-- 将搜索结果放入临时表中,表名随机生成,在' FROM '前插入'INTO '+随机临时表名
SELECT @SqlStr = stuff ( @SqlStr , charindex ( ' FROM ' , upper ( @SqlStr )), 6 , ' INTO tempdb..Paging ' + @dt + ' FROM ' )
EXECUTE ( @SqlStr )
-- 为临时表增加id号
SELECT @SqlStr = ' ALTER TABLE tempdb..Paging ' + @dt + ' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY '
EXECUTE ( @SqlStr )
-- 计算临时表中的记录数
-- SELECT @SqlStr = 'SELECT Count(*) From tempdb..Paging'+@dt
-- EXECUTE (@SqlStr)
-- 选取记录号在起始行和结束行中间的记录
SELECT @SqlStr = ' SELECT * FROM tempdb..Paging ' + @dt + ' WHERE TEMPDB_ID > ' + convert ( varchar , @FirstRec ) + ' and TEMPDB_ID < ' + convert ( varchar , @LastRec )
EXECUTE ( @SqlStr )
-- 删除临时表
SELECT @SqlStr = ' DROP TABLE tempdb..Paging ' + @dt
EXECUTE ( @SqlStr )
END
/*
some comments:
1. @SqlStr varchar(8000), depends on your page size
2. this is a generic paging sp, if you just want to use it for specific table,
you'd better change the 'tempdb..Paging' to #Paging, the performance will be better
*/
--
清空日志
Dump Transaction DB_BD With truncate_only
go
use DB_BD
go
if exists ( select 1 from sysobjects
where id = object_id ( ' PR_GET_PAGESIZE ' )
and sysstat & 15 = 4 )
drop procedure PR_GET_PAGESIZE
go
create procedure PR_GET_PAGESIZE (
@SqlStr varchar ( 8000 ), -- SQL语句
@PageSize int , -- 每页记录数
@CurrentPage int -- 当前页数
)
as
begin
declare @FirstRec int , -- 页起始行
@maxCount int , -- 页结束行
@dt varchar ( 10 ) -- 生成临时表的随机数
select @FirstRec = ( @CurrentPage - 1 ) * @PageSize -- 计算页起始行
-- 将搜索结果放入临时表中,表名随机生成,在' from '前插入'into '+随机临时表名
select @SqlStr = stuff ( @SqlStr , charindex ( ' from ' , lower ( @SqlStr )), 6 ,
' , TEMPDB_ID = identity(11) into #temp_page from ' )
-- 设置结束行
select @maxCount = @PageSize * @CurrentPage
select @SqlStr = ' set rowcount ' + convert ( varchar , @maxCount )
+ ' ' + @SqlStr
+ ' set rowcount 0 '
-- 选取记录号在起始行和结束行中间的记录
select @SqlStr = @SqlStr + ' select * from #temp_page where TEMPDB_ID > ' + convert ( varchar , @FirstRec )
print ' %1! ' , @SqlStr
execute ( @SqlStr )
end
go
sp_procxmode PR_GET_PAGESIZE, anymode
go
Dump Transaction DB_BD With truncate_only
go
use DB_BD
go
if exists ( select 1 from sysobjects
where id = object_id ( ' PR_GET_PAGESIZE ' )
and sysstat & 15 = 4 )
drop procedure PR_GET_PAGESIZE
go
create procedure PR_GET_PAGESIZE (
@SqlStr varchar ( 8000 ), -- SQL语句
@PageSize int , -- 每页记录数
@CurrentPage int -- 当前页数
)
as
begin
declare @FirstRec int , -- 页起始行
@maxCount int , -- 页结束行
@dt varchar ( 10 ) -- 生成临时表的随机数
select @FirstRec = ( @CurrentPage - 1 ) * @PageSize -- 计算页起始行
-- 将搜索结果放入临时表中,表名随机生成,在' from '前插入'into '+随机临时表名
select @SqlStr = stuff ( @SqlStr , charindex ( ' from ' , lower ( @SqlStr )), 6 ,
' , TEMPDB_ID = identity(11) into #temp_page from ' )
-- 设置结束行
select @maxCount = @PageSize * @CurrentPage
select @SqlStr = ' set rowcount ' + convert ( varchar , @maxCount )
+ ' ' + @SqlStr
+ ' set rowcount 0 '
-- 选取记录号在起始行和结束行中间的记录
select @SqlStr = @SqlStr + ' select * from #temp_page where TEMPDB_ID > ' + convert ( varchar , @FirstRec )
print ' %1! ' , @SqlStr
execute ( @SqlStr )
end
go
sp_procxmode PR_GET_PAGESIZE, anymode
go