如题,[使用table:EMP]
列出薪资(SAL)相等之员工信息,并依SAL由大而小(descending)排序。
(注:如SCOTT与FORD均为3000元; WARD与MARTIN均为1250元,
故这些员工皆应列出) EMPNO ENAME SAL DEPTNO
--------- ---------- --------- ---------
7788 SCOTT 3000 20
7902 FORD 3000 20
7521 WARD 1250 30
7654 MARTIN 1250 30
使用PL/SQL语言写出,不能使用GROUP BY 和HAVING,以及多表连接的方法.
单纯使用PL/SQL语言.
多谢啊
列出薪资(SAL)相等之员工信息,并依SAL由大而小(descending)排序。
(注:如SCOTT与FORD均为3000元; WARD与MARTIN均为1250元,
故这些员工皆应列出) EMPNO ENAME SAL DEPTNO
--------- ---------- --------- ---------
7788 SCOTT 3000 20
7902 FORD 3000 20
7521 WARD 1250 30
7654 MARTIN 1250 30
使用PL/SQL语言写出,不能使用GROUP BY 和HAVING,以及多表连接的方法.
单纯使用PL/SQL语言.
多谢啊
from
(
select a.*,count(empno) over(partition by sal) num
from emp a
)
where num>1
from
(
select emp.*,count(empno) over(partition by sal) num
from emp
)
where num>1
select *
from
(
select a.*,count(empno) over(partition by sal) num
from emp a
)
where num>1
order by SAL desc
create table emp
(EMPNO ,ENAME, SAL ,DEPTNO
)
as
select '7788','SCOTT',3000,20 from dual
union
select '7521','WARD',1250,30 from dual
union
select '7654','MARTIN',1250,30 from dual
union
select '7903','FORD',3100,20 from dual
union
select '7904','FORD2',3100,20 from dual
select *
from
(
select a.*,count(empno) over(partition by sal) num
from emp a
)
where num>1
order by SAL DESC--结果:
7903 FORD 3100 20 2
7904 FORD2 3100 20 2
7521 WARD 1250 30 2
7654 MARTIN 1250 30 2