8*.[使用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
列出薪资(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
SQL> create table tbl(empno int,ename nvarchar2(20),sal int,deptno int);Table createdSQL> insert into tbl select 7788,'scott',3000,20 from dual;1 row insertedSQL> insert into tbl select 7902,'ford',3000,20 from dual;1 row insertedSQL> insert into tbl select 7521,'ward',1250,20 from dual;1 row insertedSQL> insert into tbl select 7654,'martin',1250,20 from dual;1 row insertedSQL> insert into tbl select 7789,'scott1',2000,20 from dual;1 row inserted
SQL> select empno,ename,sal,deptno from (
2 select empno,ename,sal,deptno,count(1)over(partition by sal) cnt from tbl
3 ) where cnt>1 order by sal desc; EMPNO ENAME SAL DEPTNO
--------------------------------------- ---------------------------------------- --------------------------------------- ---------------------------------------
7902 ford 3000 20
7788 scott 3000 20
7521 ward 1250 20
7654 martin 1250 20
2 select empno,ename,sal,deptno,count(1)over(partition by sal) cnt from tbl
3 ) where cnt>1 order by sal desc;
算做连接吗?
SQL> SELECT *
2 FROM EMP
3 ; EMPNO ENAME SAL DEPTNO
---------- ------ ---------- ----------
7788 SCOTT 3000 20
7902 FORD 3000 20
7928 MANTIS 3000 30
7521 WARD 1250 30
7654 MARTIN 1250 30
7763 GOGER 7000 206 rows selected
如果是在同一个部门下面比较:SQL> SELECT *
2 FROM EMP T1
3 WHERE EXISTS (
4 SELECT 1
5 FROM EMP T2
6 WHERE T2.SAL = T1.SAL
7 AND T2.EMPNO <> T1.EMPNO
8 AND T2.DEPTNO = T1.DEPTNO
9 )
10 ORDER BY T1.SAL DESC;; EMPNO ENAME SAL DEPTNO
---------- ------ ---------- ----------
7902 FORD 3000 20
7788 SCOTT 3000 20
7654 MARTIN 1250 30
7521 WARD 1250 30
如果不是在同一个部门下面比较:SQL> SELECT *
2 FROM EMP T1
3 WHERE EXISTS (
4 SELECT 1
5 FROM EMP T2
6 WHERE T2.SAL = T1.SAL
7 AND T2.EMPNO <> T1.EMPNO
8 )
9 ORDER BY T1.SAL DESC; EMPNO ENAME SAL DEPTNO
---------- ------ ---------- ----------
7928 MANTIS 3000 30
7902 FORD 3000 20
7788 SCOTT 3000 20
7654 MARTIN 1250 30
7521 WARD 1250 30
http://topic.csdn.net/u/20080806/17/6ee006b9-7b46-442d-9dd4-d751ee0a3181.html