以oracle自带的scott为例:
1、select
t1.ename,t2.ename
as
mgrname,d.dname
from
scott.emp
t1,scott.emp
t2,scott.dept
d
where
t1.mgr=t2.empno
and
t2.deptno=d.deptno
2、select
d.*
from
scott.dept
d,(select
rownum
n,
tt.*
from
(select
count(*)
c,deptno
from
scott.emp
group
by
deptno
order
by
c
desc)
tt)t
where
t.n=1
and
t.deptno=d.deptno
3、select
*
from(select
t.*,dense_rank()over
(partition
by
deptno
order
by
sal)
r
from
scott.emp
t
where
deptno=30)
where
r<=3
4、select
*
from(select
t.*,rank()over
(partition
by
n
order
by
sal)
r
from
(select
''
as
n,tt.*
from
scott.emp
tt)
t)
where
r
between
5
and
10
温馨提示:答案为网友推荐,仅供参考