版本12.5.2，分页取数据的sql怎么写？谁能帮我

hannibal

一段很好的分页程序，速度很快，sybase内部员工写的，改成自己需要的SQL吧。

create procedure test_p @ipage int, @num int as   /* @ipage  页码, @num 每页的记录数 */
begin
        declare @maxpages int, @rcount int   /* @maxpages 最大页码 */
        if @ipage>=100
                select @maxpages=ceiling(count(*)/@num) from test
        else
                select @maxpages=100000
        if @ipage<=@maxpages/2
        begin
                select @rcount=@ipage*@num
                set rowcount @rcount
                select id=identity(12),name,descs,ddd into #temptable1 from test order by id
                select * from #temptable1  where id>=(@ipage-1)*@num and id<= @ipage*@num
        end else
        begin
                select @rcount=(@maxpages-@ipage+1)*@num
                set rowcount @rcount
                select id=identity(12),name,descs,ddd into #temptable2 from test order by id desc
                select id,name, ddd,descs from #temptable2  where id>=(@maxpages-@ipage)*@num and id<= (@maxpages-@ipage+1)*@num                         order by id  desc
        end
end

hannibal

好事做到底，送个通用版吧

create procedure splitpage @qry varchar(16384),@ipage int, @num int as   /*@qry SQL语句, @ipage 页数, @num 每页记录条数 */
begin
        declare @maxpages int
        declare @rcount int
        declare @execsql varchar(16384)

        if @ipage>=100
                select @maxpages=ceiling(count(*)/@num) from test
        else
                select @maxpages=100000
        if @ipage<=@maxpages/2
        begin
                select @rcount=@ipage*@num
                set rowcount @rcount
                set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
                set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
                set @execsql = @execsql || ' select * from #temptable1  where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
                execute (@execsql)

        end else
        begin
                select @rcount=(@maxpages-@ipage+1)*@num
                set rowcount @rcount
                set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
                set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
                set @execsql = @execsql || ' order by sybid desc'
                set @execsql = @execsql || ' select * from #temptable1 where sybid > ' || convert(varchar,(@maxpages-@ipage)*@num) || ' and sybid <= ' || convert(varchar,(@maxpages-@ipage+1)*@num)
                execute (@execsql)
        end
end

vct00

>select id=identity(12),name,descs,ddd into #temptable1 from test order by id
>select * from #temptable1  where id>=(@ipage-1)*@num and id<= @ipage*@num
我在项目中也是使用这个模式的，问题是写临时表时是否会发生物理写操作？如果发生，还有没有不需要写临时表的方案？

ncowboy

原帖由 hannibal 于 2005-12-14 17:38 发表
好事做到底，送个通用版吧

create procedure splitpage @qry varchar(16384),@ipage int, @num int as   /*@qry SQL语句, @ipage 页数, @num 每页记录条数 */
begin
        declare @maxpages int
        ...

18:38:25.968        DBMS        sybase@jasmine -- Error:  Number (102) Severity (15) State (1) Server (Jasmine) Procedure (splitpage) 在 '@execsql'附近有不正确的语法。 (42000), Batch 1 Line 15
18:38:25.984        DBMS        sybase@jasmine -- Error:  Number (102) Severity (15) State (1) Server (Jasmine) Procedure (splitpage) 在 '@execsql'附近有不正确的语法。 (42000), Batch 1 Line 24

hannibal

vc00, sybase中的临时表当用select into #table的形式时，是不记录日志的。速度非常快。
这点和oracle不同。算是sybase的一个feature.

ncowboy ，老程序在处理10万页以上结果集会出现问题。用select @maxpages=ceiling(count(*)/@num) from test此处使用是不对的。
由于即席查询时获取maxpages效率不高，对此作以下修改：可以由用户指定最大查询页数，缺省定为5000

create procedure splitpage @qry varchar(16384),@ipage int, @num int,@maxpages int = 5000 as  
/*@qry SQL语句, @ipage 页数, @num 每页记录条数, @maxpages 最大查询页数 */
begin

        declare @rcount int
        declare @execsql varchar(16384)

        if @ipage > @maxpages
        begin
              select '输入页数[' || convert(varchar,@ipage) || ']大于最大查询页数[' ||  convert (varchar,@maxpages) ||']'
              return
        end

        select @rcount=@ipage*@num
        set rowcount @rcount
        set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
        set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
        set @execsql = @execsql || ' select * from #temptable1  where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
        execute (@execsql)
end

由于无法精确且高效取出即席查询结果集的总页数，原程序中考虑后几页查询的优化算法就无法使用了。
如果可以先算出精确的总页数，然后作为参数传给splitpage,那么也可以把查询后几页的算法再补上。

谁有更好的办法也可以提出来大家一起把这个分页存储过程优化到底。

vct00

我在测试 select id=identity(12),name,descs,ddd into #temptable1 from test order by id 时，
系统提示：
Total writes for this command: 16
这里是否指物理写操作呢？

欣语欣愿

原帖由 leno_mx 于 2005-12-14 10:18 发表
top在ASE 15可以支持，但不知道start at支不支持？

不行，top不能在ASE 15上使用，我觉得应该先定义游标！这样作速度快，而且稳定！

leno_mx

楼上用游标是最慢的呀。怎么快啊？top怎么在ASE15上不行。你怎么写的sql?

networkiller

select * from 表名 (where条件) limit 10,20;

hannibal

版本更新：1.0.2
注意程序最后要加上set rowcount 0

create procedure splitpage @qry varchar(16384),@ipage int, @num int,@maxpages int = 5000 as  
/*@qry SQL语句, @ipage 页数, @num 每页记录条数, @maxpages 最大查询页数 */
begin

        declare @rcount int
        declare @execsql varchar(16384)

        if @ipage > @maxpages
        begin
              select '输入页数[' || convert(varchar,@ipage) || ']大于最大查询页数[' ||  convert (varchar,@maxpages) ||']'
              return
        end

        select @rcount=@ipage*@num
        set rowcount @rcount
        set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
        set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
        set @execsql = @execsql || ' select * from #temptable1  where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
        execute (@execsql)
        set rowcount 0
end