oracle中查询第一名,最后一名 和中间名次的方法

在ORACLE中查询一组数据中的最大值　select max(age) from student;

查询最小值　select min(age) from student;

 

 

查询第５名方法：

　第一步　建立视图　将年龄排序好了放在视图中　

create or replace view sha as select age from student order by age ASC;

 

   第二步　用rownum（sql用top）  取出数据

select name from sha where rownum<=1 and age not in(select name from sha where rownum<=5);

 

 

另一种更完美的方法

 測試數據
DROP TABLE TEST;
CREATE TABLE TEST (ID NUMBER PRIMARY KEY, NAME VARCHAR2(20), SCORE NUMBER);

INSERT INTO TEST (ID, NAME, SCORE)
VALUES (1, 'A', 99);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (2, 'B', 45);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (3, 'C', 100);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (4, 'D', 60);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (5, 'E', 88);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (6, 'F', 99);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (7, 'G', 78);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (8, 'H', 80);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (9, 'I', 95);
INSERT INTO TEST (ID, NAME, SCORE)
VALUES (10, 'J', 95);
COMMIT;

SELECT ID, NAME, SCORE, 排名
FROM (
SELECT ID, NAME, SCORE, RANK() OVER (ORDER BY SCORE DESC) "排名"
FROM TEST
)
WHERE 排名 = 2;

        ID NAME                                SCORE         排名
---------- ------------------------------ ---------- ----------
         1 A                                      99          2
         6 F                                      99          2