select * from (select rownum rn,* from (select col_name from test group by 列1 order by 列2)) where rn=10
select rownum rn,* from table貌似不能这样吧,应该会有缺乏什么的提示吧, select * from (select rownum rn,a.* from (select col_name from test group by 列1 order by 列2)) a where rn=10
用分析函数: dense_rank(order by columnName) --对所有记录按照你的要求字段进行排序,然后取排序后=10的记录
SELECT * FROM ( select deptno,ename,sal,dense_rank() over (order by sal) rank from emp ) WHERE RANK =10 ;以上语句:取通过sal排序后排列第十的记录
rownum 应该是变化的吧 select col_name from test group by 列1 order by 列2 这样以后 应该rownum已经不是以前的rownum了?
select * from (select rownum rn,* from (select 列1 ,列2 from test group by 列1 order by 列2)) where rn=10
第一种 select * from (select rownum rn,a.* from emp a ) where rn=10; 第二种 select * from emp where rownum <11 minus select * from emp where rownum<10; 第三种 select * from (select deptno,ename,sal,dense_rank() over (order by deptno) rank from emp) where rank=10; 藐视:第一种和第二种可以,但第三种因为涉及到排序,数据就跟前两种得到的数据不一致了,不过理论上讲如果是都有排序,这三种方法都可以实现的。第一种和第三种经常用,不过第二种还真没怎么用过,张见识了。非常感谢各位大大。
select * from emp where rowid not in (select rowid from emp where rownum<10) and rownum <11
Here what's the value of "group by"?
谢谢所有回帖的朋友,确实是rownum的位置的问题,rownum应该放在外层,而且要给子查询添加别名! 如: select * from (select rownum rn,a.* from (select col_name from test group by 列1 order by 列2)) a where rn=10还有一种方法就是 先按照升序去前10条记录,再把结果按降序排序,取第一条记录就是第十条了,前提条件是全部记录超过十条以上,不然结果不正确 select* from( select* from( select col_name from test group by 列1 order by 列2 asc) where rownum<10 order by 列2 desc) where rownum<2
SQL> select rownum rn,a.*
2 from (
3 select e.*
4 from emp e
5 order by e.sal) a
6 /
RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
1 7369 SMITH CLERK 7902 1980-12-17 800.00 20
2 7900 JAMES CLERK 7698 1981-12-03 950.00 30
3 7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
4 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
6 7934 MILLER CLERK 7782 1982-01-23 1300.00 10
7 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
8 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
9 7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
10 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
11 7566 JONES MANAGER 7839 1981-04-02 2975.00 20
12 7902 FORD ANALYST 7566 1981-12-03 3000.00 20
13 7788 SCOTT ANALYST 7566 1987-04-19 3100.00 20
14 7839 KING PRESIDENT 1981-11-17 5000.00 10
14 rows selected
SQL> select * from (
2 select rownum rn,a.*
3 from (
4 select e.*
5 from emp e
6 order by e.sal) a)
7 where rn=10
8 /
RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
10 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
select *
from (select rownum rn,* from
(select col_name
from test
group by 列1 order by 列2))
where rn=10
select *
from (select rownum rn,a.* from
(select col_name
from test
group by 列1 order by 列2)) a
where rn=10
dense_rank(order by columnName) --对所有记录按照你的要求字段进行排序,然后取排序后=10的记录
SELECT * FROM (
select deptno,ename,sal,dense_rank() over (order by sal) rank from emp
) WHERE RANK =10 ;以上语句:取通过sal排序后排列第十的记录
select col_name
from test
group by 列1 order by 列2
这样以后 应该rownum已经不是以前的rownum了?
(select 列1 ,列2
from test
group by 列1 order by 列2))
where rn=10
6楼是正确的
楼主的那种写法是先生成rownum序号列在排序,所以子查询的第一条数据rownum可能不是1
select * from test d where rownum < 10;
第二种 select * from emp where rownum <11 minus select * from emp where rownum<10;
第三种 select * from (select deptno,ename,sal,dense_rank() over (order by deptno) rank from emp)
where rank=10;
藐视:第一种和第二种可以,但第三种因为涉及到排序,数据就跟前两种得到的数据不一致了,不过理论上讲如果是都有排序,这三种方法都可以实现的。第一种和第三种经常用,不过第二种还真没怎么用过,张见识了。非常感谢各位大大。
如:
select *
from (select rownum rn,a.* from
(select col_name
from test
group by 列1 order by 列2)) a
where rn=10还有一种方法就是 先按照升序去前10条记录,再把结果按降序排序,取第一条记录就是第十条了,前提条件是全部记录超过十条以上,不然结果不正确
select* from(
select* from(
select col_name
from test
group by 列1 order by 列2 asc)
where rownum<10 order by 列2 desc) where rownum<2