Chinaunix

标题: 版本12.5.2，分页取数据的sql怎么写？谁能帮我 [打印本页]

作者: iim_liyongfeng 时间: 2005-12-13 14:23
标题: 版本12.5.2，分页取数据的sql怎么写？谁能帮我
版本12.5.2，分页取数据的sql怎么写？

作者: leno_mx 时间: 2005-12-13 16:14
不明白什么意思？

作者: iim_liyongfeng 时间: 2005-12-13 16:23
标题: 取第11条到第20条的记录
取第11条到第20条的记录：
象mysql一样，select * from table where c1='a' start 11 limit 10

作者: flybean 时间: 2005-12-13 17:29
set rowcount 20
-- get 20 rows
-- discard the first 10 rows
set rowcount 0

作者: ncowboy 时间: 2005-12-13 18:18

原帖由 flybean 于 2005-12-13 17:29 发表
set rowcount 20
-- get 20 rows
-- discard the first 10 rows
set rowcount 0

不能够设置起始位置啊。哥哥。

作者: ncowboy 时间: 2005-12-13 18:20
可是，这个不能够用。

显式限制查询返回的行数

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

您可以使用 FIRST 或 TOP 关键字限制查询的结果集中包括的行数。这些关键字用于包括 ORDER BY 子句的查询。

示例
以下查询返回在按姓氏对雇员进行排序时首先出现的雇员的信息：

SELECT FIRST *
FROM employee
ORDER BY emp_lname
以下查询返回按姓氏排序时出现的前五个雇员：

SELECT TOP 5 *
FROM employee
ORDER BY emp_lname
在使用 TOP 时，您也可以使用 START AT 来提供偏移。以下语句列出按姓氏以降序进行排序时出现的第五个和第六个雇员：

SELECT TOP 2 START AT 5 *
FROM employee
ORDER BY emp_lname DESC
FIRST 和 TOP 只能与 ORDER BY 子句联合使用，以确保获得一致的结果。如果使用 FIRST 或 TOP 时没有 ORDER BY，则会触发语法警告，并且很可能产生无法预知的结果。

注意
'start at' 值必须大于 0。当 'top' 为常量时，其值必须大于 0；当 'top' 为变量时，其值必须大于或等于 0。

作者: zhhui2000 时间: 2005-12-13 18:38
SYBASE 不提供这种方式的提取，除非自己写一个游标

作者: iim_liyongfeng 时间: 2005-12-13 19:33
top 语句不行啊

1> select top 10 * from tblStudentCode order by Code
2> go
Msg 102, Level 15, State 1:
Server 'DBSVR', Line 1:
Incorrect syntax near '10'.

作者: jarjar 时间: 2005-12-13 21:47
top语句在12.5.3之后才支持，实际上使用set rowcount n是个不错的选择

作者: leno_mx 时间: 2005-12-14 10:18
top在ASE 15可以支持，但不知道start at支不支持？

作者: hannibal 时间: 2005-12-14 16:51
一段很好的分页程序，速度很快，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 时间: 2005-12-14 17:38
好事做到底，送个通用版吧

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 时间: 2005-12-15 08:28
>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 时间: 2005-12-15 18:39

原帖由 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 时间: 2005-12-16 11:18
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 时间: 2005-12-16 11:57
我在测试 select id=identity(12),name,descs,ddd into #temptable1 from test order by id 时，
系统提示：
Total writes for this command: 16
这里是否指物理写操作呢？

作者: 欣语欣愿 时间: 2005-12-22 12:00

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

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

作者: leno_mx 时间: 2005-12-22 16:35
楼上用游标是最慢的呀。怎么快啊？top怎么在ASE15上不行。你怎么写的sql?

作者: networkiller 时间: 2006-04-05 00:10
标题: mysql
select * from 表名 (where条件) limit 10,20;

作者: hannibal 时间: 2006-04-24 11:20
版本更新：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

作者: willbeike 时间: 2007-11-22 11:10
存储过程应该如何调用啊？搞了半天不知道如何使用，郁闷!谢谢了

作者: mcolinc 时间: 2008-02-21 20:32
创建表的时候设置一个identity的栏位
这样就可以做到分页取数据了

作者: aliking 时间: 2008-02-26 20:41
同意17楼的观点，用游标更好一些。
用临时表算法的空间复杂度是O(n)，如果表很大或没有预存在Cache中，所用的I/O时间就非常可观了。
用游标定位到指定的页，空间复杂度应该是O(1)，虽然游标遍历记录比直接用Select稍慢一点，但考虑到分页应用中，每次只读取一页的内容，所以这个时间性能上的损失可以忽略。

作者: gelyon 时间: 2011-11-18 13:59
虽然这个帖子已经很久了，但是小弟现在遇到个关于sybase临时表分页的问题，对于两个结果集union 后分页不正确。
例如：
SELECT row_num=identity(16),tmpData.* into #tmptalbe from
(
SELECT ACCOUNT_ID ,CARD_ID FROM T_ACCOUNT_CARD
UNION ALL
SELECT USER_ID,ROLE_ID FROM T_USER_ROLE
) tmpData
select * from #tmptalbe
--where row_num>=convert(int,1) and row_num<=convert(int,20)
drop table #tmptalbe

这样分页后不正确，因为union all 后面第二个结果集没有被分配row_num，始终是0，导致我分页不正确。

我尝试改写union all写法，用full join 来实现，但是测试时才发现sybase居然不支持full join，郁闷到了。。
SELECT
      CASE WHEN A.ACCOUNT_ID IS NULL THEN B.USER_ID ELSE A.ACCOUNT_ID END ID1,
      CASE WHEN A.CARD_ID IS NULL THEN B.ROLE_ID ELSE A.CARD_ID END ID2
FROM T_ACCOUNT_CARD A FULL JOIN T_USER_ROLE B ON 1=2

另外我也测试了用视图来做，结果也不行，视图中用UNION ALL 连接的两个结果集，第二个结果集还是没有ROW_NUM，始终为0，导致sybase临时表分页也不正确。

CREATE VIEW V_TEST  AS(
SELECT ACCOUNT_ID ,CARD_ID FROM T_ACCOUNT_CARD
UNION ALL
SELECT USER_ID,ROLE_ID FROM T_USER_ROLE
)

SELECT row_num=identity(16),tmpData.* into #tmptalbe from
(
SELECT * FROM V_TEST
) tmpData
select * from #tmptalbe
--where row_num>=convert(int,1) and row_num<=convert(int,20)
drop table #tmptalbe

请问怎么来结果这个分页查询不正确，到底union all 后的第二个结果集为什么row_num始终是0，而不是递增的顺序值呢？

作者: epstar 时间: 2012-10-27 17:26
一楼的位置好啊..

作者: Eisen 时间: 2012-10-28 14:00
本帖最后由 Eisen 于 2012-10-28 14:00 编辑

gelyon 发表于 2011-11-18 13:59
虽然这个帖子已经很久了，但是小弟现在遇到个关于sybase临时表分页的问题，对于两个结果集union 后分页不正 ...

那换个写法呢--
create table #tmptable (row_num int identity,account_id varchar( 8 ),card_id varchar(20))
go
insert #tmptable
SELECT ACCOUNT_ID ,CARD_ID FROM T_ACCOUNT_CARD
UNION ALL
SELECT USER_ID,ROLE_ID FROM T_USER_ROLE

select * from #tmptable where row_num>=convert(int,1) and row_num<=convert(int,20)

作者: 2BeSybPro 时间: 2012-10-30 00:14
我n年前写过一个通用的分页程序，见下面链接。
http://bbs.chinaunix.net/forum.p ... p;page=1#pid3779459

用tempdb作为staging area，一定注意别把tempdb填满了。Temp table 最好建索引，插入temp table的语句一定要用index scan，否则会在tempdb中hold transaction而引起tempdb space issue。

作者: wang_xue_m 时间: 2012-10-30 09:03
楼主，用BCP可以设置起始

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)