比如某个表 a 里面有个字段code 为无规律的数据 如 4,8,2,9,32,0,12 等.如何取得从高到低排序后的前10条记录?
我用 select * from a where rownum <= 10 order by code desc; 却得不到正确结果.然后用 select * from (select * from a order by code) where rownum <= 10; 提示missing right parenthesis的错误.
多谢!
我用 select * from a where rownum <= 10 order by code desc; 却得不到正确结果.然后用 select * from (select * from a order by code) where rownum <= 10; 提示missing right parenthesis的错误.
多谢!
/
insert into t55
select 1 from dual union all
select 3 from dual union all
select 2 from dual union all
select 4 from dual;
/
commit;
/
select code from
(select t55.*,rownum num from t55 order by code desc) t where t.num<=10;
/
--输出结果
4
3
2
1
MS SQL Server的top+order在经过排序后在取得前n行数据,
而Oracle先取得前n行后在进行排序(等于没效果~)
因此要用两层select:
select *
from (select * from table_name order by col) a
where rownum<=n
不过可再介绍下关于取第N条纪录到第N+M条纪录的方法及ROW_NUMBER()的用法
这点用过之后你会觉得比ORACLE比SQL SERVER的TOP强
select code from
(select t55.*,rownum num from t55 order by code desc) t where t.num <=10;
直接在1楼的上面改
select code from
(select t55.*,rownum num from t55 order by code desc) t
where t.num >=n and t.num<=n+m;
因为楼主是要排序再取,
如果不排序取,可以更多的滤掉一部分数据
select code from
(select t55.*,rownum num from t55 rownum<=n+m) t
where t.num >=n
;
为什么要分两层呢,因为rownum本身是个伪列,本身只支持<=N
要想支持>=n and <=n+m的话,先要转成实列,上面的rownum num这段就是的还可以用另外种做法,row_number() over
例子:
select code from
(select t55.*,row_number() over(order by code desc) num from t55 ) t
where t.num >=n and t.num<=n+m;
row_number() over在优势在于可以分组,比如说取分组后的n到n+m名select code from
(select t55.*,row_number() over(partition by 分组字段 order by code desc) num from t55 ) t
where t.num >=n and t.num<=n+m
这样就能取出分组后的n到n+m条
你怎么老记着强分?