有一张放新闻记录的表news,有字段id,bt(标题),sj(时间),lb(类别)
现在要取按类别升序,时间降序后,各类别的记录10条,写查询语句。
用select id,bt,sj,lb from
(select news.*,rank() over(partition by lb order by sj desc) rk from news) t
where rk<=10 order by lb;
提示:提示出错:ORA-00439:未启用特征:OLAP WINDOW FUNCTIONS
不支持分析函数。
select * from news t
where (select count(*) from news tt where tt.sj<=t.sj and t.lb=tt.lb)<11;
速度太慢了。
请问该如何处理?
现在要取按类别升序,时间降序后,各类别的记录10条,写查询语句。
用select id,bt,sj,lb from
(select news.*,rank() over(partition by lb order by sj desc) rk from news) t
where rk<=10 order by lb;
提示:提示出错:ORA-00439:未启用特征:OLAP WINDOW FUNCTIONS
不支持分析函数。
select * from news t
where (select count(*) from news tt where tt.sj<=t.sj and t.lb=tt.lb)<11;
速度太慢了。
请问该如何处理?
17:28:58 SQL> select * from tb;COL1 COL2 COLNEW
---------- ------------------------------ --------------------
1 aaa aaa
2 aaa aaa
3 aaa aaa
4 bba bba
5 bbb bbb
8 bbb ccc已选择6行。已用时间: 00: 00: 00.47
17:29:08 SQL> select * from (
17:29:13 2 select t1.col1,t1.col2,t1.id-t2.mid id from
17:29:13 3 (select rownum id,t.* from(
17:29:13 4 select * from tb order by col2,col1 desc) t) t1,
17:29:13 5 (select col2,min(id) mid from (
17:29:13 6 select rownum id,t.* from(
17:29:13 7 select * from tb order by col2,col1 desc) t) group by col2) t2
17:29:13 8 where t1.col2=t2.col2(+)
17:29:13 9 ) where id<2;COL1 COL2 ID
---------- ------------------------------ ----------
3 aaa 0
2 aaa 1
4 bba 0
8 bbb 0
5 bbb 1已用时间: 00: 00: 00.31
17:29:14 SQL>