-- 測試數據 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
select (select *, dense_rank() over(order by cj desc ) rn from tb ) where rn=2
create table xscj( name varchar2(50), cj number )insert into xscj values('张三',100); insert into xscj values('张三1',100); insert into xscj values('张三2',100); insert into xscj values('张三3',100); insert into xscj values('李四',101); insert into xscj values('李四1',101); insert into xscj values('李四2',101); insert into xscj values('李四3',101); insert into xscj values('王二',103); insert into xscj values('王二1',131); insert into xscj values('王二2',151); insert into xscj values('王二3',171); select * from xscj s where s.cj = (select min(cj) from (select min(cj) x from xscj) a, xscj where xscj.cj > a.x)
select xscj.* from xscj, (select cj, rank() over(order by cj) pm from (select distinct cj from xscj)) a where a.cj = xscj.cj and a.pm = 2
select * from (select name, cj, dense_rank() over(order by cj) pm from xscj s) where pm = 2
-- 測試數據
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
(select *,
dense_rank() over(order by cj desc ) rn
from tb
)
where rn=2
name varchar2(50),
cj number
)insert into xscj values('张三',100);
insert into xscj values('张三1',100);
insert into xscj values('张三2',100);
insert into xscj values('张三3',100);
insert into xscj values('李四',101);
insert into xscj values('李四1',101);
insert into xscj values('李四2',101);
insert into xscj values('李四3',101);
insert into xscj values('王二',103);
insert into xscj values('王二1',131);
insert into xscj values('王二2',151);
insert into xscj values('王二3',171);
select *
from xscj s
where s.cj = (select min(cj)
from (select min(cj) x from xscj) a, xscj
where xscj.cj > a.x)
from xscj,
(select cj, rank() over(order by cj) pm
from (select distinct cj from xscj)) a
where a.cj = xscj.cj
and a.pm = 2
from (select name, cj, dense_rank() over(order by cj) pm from xscj s)
where pm = 2