论坛 › Sybase › 请教关于ASE中子查询使用top n语句的问题

xshadows 发表于 2011-05-10 12:52

请教关于ASE中子查询使用top n语句的问题

本帖最后由 xshadows 于 2011-05-10 13:26 编辑

我在开发的过程中遇到了这样一个问题，程序需要实现的功能为：在数据表中查询每个类别的前n条数据及其均值,数据表内容类似如下结构:

AMBADATE   AMBANODEID   AMBARUNTIME   
--------------------------------------
20110319   000            7            
20110320   000            8            
20110321   000            9            
20110319   004            0            
20110320   004            1            
20110321   004            0            
20110319   006            0            
20110320   006            0            
20110321   006            0            
20110319   009            0            
20110320   009            0            
20110321   009            0            
20110319   015            124            
20110320   015            100            
20110321   015            108            
20110319   016            115            
20110320   016            101            
20110321   016            126            
20110319   017            99            
20110320   017            93            
20110321   017            107            


我写了类似于如下的语句：

select a.AMBADATE,a.AMBANODEID,a.AMBARUNTIME,avg(a.AMBARUNTIME) as avg10
from AMBA as a
where a.AMBADATE in
(
    select top 10 b.AMBADATE
    from AMBA b
    where b.AMBANODEID=a.AMBANODEID
    order by b.AMBADATE
)
group by a.AMBANODEID

不过在Sybase 中貌似子查询无法使用top n语句和order by?

请教各位是否有变通的办法能够实现这个功能？谢谢大家！！

ps.正在使用的版本是15.0.1,有top的功能但是貌似无法在子查询中使用..

zq5143 发表于 2011-05-10 13:10

12.5.4以前的版本是没有top n
可以用先执行：set rowcount 50
就可以显示前50条记录。

xshadows 发表于 2011-05-10 13:25

回复 2# zq5143

忘记说了，我用的版本是15.0.1,有top n的功能但是子查询无法用。。。。

andkylee 发表于 2011-05-11 08:26

本帖最后由 andkylee 于 2011-05-11 08:28 编辑

ASE中数据结果集分页功能的三种实现方法
第一种：利用游标

程序开发人员比较喜欢使用游标，因为游标的“循环”遍历方式类似编程语言中的for，while，loop语句的实现方法，写起来比较容易。使用游 标一般步骤是：为指定的SQL语句定义一个游标，打开并移动游标，当移动到指定行号的记录行之后，再按照需要提取的行数来取数据。从表面上看解决了提取指 定范围数据的问题；但是在实际应用 上，有可能会出现严重的性能问题。建立游标需要耗用一定的系统资源之外；当表内的数据量有上千万甚至到亿级别并且需要取大量的数据结果时，用游标每移动一 次就取这行数据，然后再移动游标，这个过程将是缓慢的。在使用游标的过程中，系统会给相应的表加上共享锁，导致锁竞争而严重影响数据库的性能。

在此不再介绍游标的实现方式，此法比较简单。

第二种：利用临时表和标志列

在Sybase ASE12.5.3及以后的版本中，我们可以用top关键字来限定只返回结果集的前N行数据。在ASE12.5.3之前的版本中只能用set rowcount N 的方法了。

对于取结果集的第N行至第N+M行数据的要求，我们考虑利用top来实现的话，比较容易想到的是：执行两次top，再加l两次倒序排序。

步骤如下：

　　　　(1)　select top N+M * from table_name where_clause order by ID   把此结果集派生为表:table_name1

                  (2)   select top M * from table_name1 order by IDDESC   把此结果集派生为表：table_name2

                  (3)   select * from table_name2 order by IDDESC

上面的3条语句好像能够实现返回第N行至第N+M行数据的要求。但是，在Sybase ASE中仅仅利用派生表而不利用临时表是不能实现这个要求的。

仅仅是ASE中的“派生出派生表(derived table)的SQL语句中不能含有order by 子句”这个限制就足以使上面的方法行不通。还有一个限制是，上面的3个步骤中都利用ID列进行排序。如果表中没有可用的排序列时，那么上述方法也不能用 了。不过幸运的是，一般要求对其结果集进行分页的表都是有可以用作排序的列的（数字型或者日期型）。

继续寻找一个能用的方法，下面着重介绍目前通用的ASE的分页思路。此思路的关键是产生identity自增列和临时表。

在ASE中大家要是找到了不用临时表就可以实现分页的方法请麻烦告诉我一声。 我尝试了很多次，都不是很理想。

概括起来主要语句有两条：

                (1)   select syb=identity(10),*into #temp_table from table_name where_clause   order_by_clause

                (2)   select* from #temp_table where_clause and syb >= N   and syb <= N+M

第三种：利用rowcount

此种方法有点不足：必须利用可用作排序的列 对结果集进行排序。

还是上面的测试表testA，如果从第9000行开始选择10行数据，那么语句如下：

    declare @id1 int
    set rowcount 9000
    select @id1 = id from testA order by id
    set rowcount 10
    select *from testA where id >= @id1 order by id
    set rowcount 0
    go



此种方法中核心语句是select @id1=id from testA order by id ， 在对表testA执行查询的过程中，每读取一行都会把id列的值赋给@id1这个变量，一直持续到最后一行，@id1这个变量反复被下一行的id值刷新， 结果只得到最后一样的id值。如果在此select语句之前加上rowcount的限定，那么就可用使得@id1这个变量获得第rowcount行的id 值，那么我们也就获得了返回范围结果集的起点了。

后面的 set rowcount 10

         select * from testA where id >= @id1 order by id

这两句实际上可以用一句select top 10 * from testA where id >= @id1 order by id来替代。

这样，两种不同的实现形式为:

    declare @id1 int
    set rowcount 9000
    select @id1 = id from testAorder by id
    set rowcount 0
    select top 10*from testA whereid >= @id1 order by id
    go


请参考：ASE中数据结果集分页功能的三种实现方法
http://www.dbainfo.net/ase-split-page-3ways-identity-setrowcount-temptable.htm

2BeSybPro 发表于 2011-05-13 03:17

Adaptive Server Enterprise supports the top n clause in outer query select statements, but not in the select list of a subquery.
This differs from Microsoft SQL Server. Any attempt to use the top n clause with Adaptive Server in a subquery yields a syntax error.

Eisen 发表于 2011-05-13 12:10

Adaptive Server Enterprise supports the top n clause in outer query select statements, but not in th ...
2BeSybPro 发表于 2011-05-13 03:17 http://bbs.chinaunix.net/images/common/back.gif


    这个毛病真是令人讨厌阿。

hannibal 发表于 2011-07-15 18:22

回复 4# andkylee

分页用这个通用的存储过程试试：

create procedure sp_page @qry varchar(16384),@ipage int, @num int as
begin
      declare @rcount int
      declare @execsql varchar(16384)

      select @rcount=@ipage*@num
      set @execsql = 'set rowcount ' ||convert(varchar,@rcount)
      set @execsql = @execsql || stuff(@qry,charindex('SELECT ',upper(@qry)),6,' SELECT sybid=identity(12),')
      set @execsql = stuff(@execsql, charindex(' FROM ',upper(@execsql)),6,' INTO #temptable FROM ')
      set @execsql = @execsql || ' SELECT * FROM #temptableWHERE sybid>' || convert(varchar,(@ipage-1)*@num)
      set @execsql = @execsql || ' AND sybid <= ' || convert(varchar,@ipage*@num) + ' set rowcount 0 '
      execute (@execsql)
end

查看完整版本: 请教关于ASE中子查询使用top n语句的问题