- create table t (x int); --创建测试表
- insert into t values(1);
- insert into t values(3);
- insert into t values(5);
- insert into t values(7);
- insert into t values(9);
- 法一:
- select x
- from ( select x, rownum r
- from ( select x from t order by x desc )
- where rownum <= 2 )
- where r = 2;
- 注释:子查询不进行扩展(unest),那么是先求出最里层的数据
- 法二:
- select *
- from t
- where (select count(*)
- from t t2
- where t2.x > t.x ) = 1
- /
- 注释:子查询中的条件跟其他高辈分的SELECT有关联(关联子查询)
- 法三:
- select max(x)
- from t
- where x < (select max(x) from t)
- /
- 法四:
- select x
- from ( select x, row_number() over ( order by x desc ) r
- from t
- )
- where r = 2
- /
- 注释:row_number() 是分析函数