topN查询
select * from emp where rownum=1 or rownum=2;
select * from (select * from emp order by sal desc) where rownum<5;
分页查询
-
select * from (select rownum no,e.* from(select * from emp order by sal desc) e where rownum<=5) where no>=3;
-
select * from (select rownum no,e.* from(select * from emp order by sal desc)e) where no>3 and no<=5;
exist
select * from t1 where exists (select null from t2 where y=x);
for x in (select * from t1)
loop
if(exist(select null from t2 where y=x.x))
then
output the record
end if;
end loop;
select 1 from dual where null in (0,1,2,null);
多行子查询
select * from emp