sybase 分页问题
请高手给个详细的分页方法。
还有顺便问下,如何格式化一个时间,类似于DATE_FORMAT(update_date,'%H:%i:%s'),这是Mysql的,请问sybase的怎么做?
[解决办法]
按行读取CREATE PROCEDURE GetDataByLine( --创建一个分页读取过程 @SqlStr varchar(8000), --SQL语句 @FirstRec int, --页起始行 @LastRec int --页结束行)ASDECLARE @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 size2. 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 --当前页数)ASDECLARE @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 size2. 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*/
[解决办法]
SELECT DATEFORMAT( '1989-01-01 01:02:03', 'hh:mm:ss' );