CREATE TABLE EMP(NAME VARCHAR2(10),SAL INTEGER); INSERT INTO EMP VALUES('A1',2015); INSERT INTO EMP VALUES('A8',2007); INSERT INTO EMP VALUES('A2',2014); INSERT INTO EMP VALUES('A14',2001); INSERT INTO EMP VALUES('A4',2012); INSERT INTO EMP VALUES('A13',2002); INSERT INTO EMP VALUES('MY',2011); INSERT INTO EMP VALUES('A10',2005); INSERT INTO EMP VALUES('A5',2010); INSERT INTO EMP VALUES('A12',2003); INSERT INTO EMP VALUES('A6',2009); INSERT INTO EMP VALUES('A7',2008); INSERT INTO EMP VALUES('A9',2006); INSERT INTO EMP VALUES('A11',2004); COMMIT;
SELECT NAME,SAL FROM( SELECT ROWNUM RN,NAME,SAL FROM(SELECT NAME,SAL FROM EMP ORDER BY SAL)) WHERE RN <( SELECT (RN-5) RN FROM(SELECT ROWNUM RN,NAME FROM(SELECT NAME,SAL FROM EMP ORDER BY SAL))WHERE NAME='MY');假设NAME=MY是自己,求数目的话改用select count(*)就可以了
SQL> select * from emp order by sal;NAME SAL ---------- ---------- 如来 10 八戒 100 哪吒 150 张衡 150 赵云 150 悟空 1000 张飞 1500 沙僧 1500 李白 5500 爱迪生 1550010 rows selected.Elapsed: 00:00:00.00 SQL> select name,sal,sal_sort 2 from (select name, 3 sal, 4 rank() over (order by sal)-1 sal_sort 5 from emp) 6 where sal_sort>=5;NAME SAL SAL_SORT ---------- ---------- ---------- 悟空 1000 5 张飞 1500 6 沙僧 1500 6 李白 5500 8 爱迪生 15500 95 rows selected.Elapsed: 00:00:00.00
INSERT INTO EMP VALUES('A1',2015);
INSERT INTO EMP VALUES('A8',2007);
INSERT INTO EMP VALUES('A2',2014);
INSERT INTO EMP VALUES('A14',2001);
INSERT INTO EMP VALUES('A4',2012);
INSERT INTO EMP VALUES('A13',2002);
INSERT INTO EMP VALUES('MY',2011);
INSERT INTO EMP VALUES('A10',2005);
INSERT INTO EMP VALUES('A5',2010);
INSERT INTO EMP VALUES('A12',2003);
INSERT INTO EMP VALUES('A6',2009);
INSERT INTO EMP VALUES('A7',2008);
INSERT INTO EMP VALUES('A9',2006);
INSERT INTO EMP VALUES('A11',2004);
COMMIT;
SELECT NAME,SAL FROM(
SELECT ROWNUM RN,NAME,SAL FROM(SELECT NAME,SAL FROM EMP ORDER BY SAL))
WHERE RN <(
SELECT (RN-5) RN FROM(SELECT ROWNUM RN,NAME FROM(SELECT NAME,SAL FROM EMP ORDER BY SAL))WHERE NAME='MY');假设NAME=MY是自己,求数目的话改用select count(*)就可以了
---------- ----------
如来 10
八戒 100
哪吒 150
张衡 150
赵云 150
悟空 1000
张飞 1500
沙僧 1500
李白 5500
爱迪生 1550010 rows selected.Elapsed: 00:00:00.00
SQL> select name,sal,sal_sort
2 from (select name,
3 sal,
4 rank() over (order by sal)-1 sal_sort
5 from emp)
6 where sal_sort>=5;NAME SAL SAL_SORT
---------- ---------- ----------
悟空 1000 5
张飞 1500 6
沙僧 1500 6
李白 5500 8
爱迪生 15500 95 rows selected.Elapsed: 00:00:00.00
==========================================
(
select name,sal,(select count(1) from emp tt where tt.sal<t.sal) count_sal
from emp t
)a
where a.count_sal>=5