Oracle之去重

SELECT * FROM EMP ;

		JOB           DEPTNO       SAL
		--------- ---------- ----------
		ANALYST           20       6600
		CLERK             10       1420
		CLERK             20       2090
		CLERK             20       2090
		MANAGER           10       2695
		MANAGER           20       3135
		MANAGER           20       3135
		PRESIDENT         10       5500
		SALESMAN          20       6160

SELECT * from EMP a Where rowid !=(select max(rowid) From EMP b Where  a.JOB = b.JOB And a.DEPTNO = b.DEPTNO) ;

SELECT A.JOB , A.DEPTNO, MAX(A.SAL) AS SAL FROM EMP A group by A.JOB , A.DEPTNO having COUNT(*)>1 
UNION ALL
SELECT A.JOB , A.DEPTNO, MAX(A.SAL) AS SAL FROM EMP A group by A.JOB , A.DEPTNO having COUNT(*)=1;

SELECT * FROM
(
 SEELCT  A.JOB , A.DEPTNO, A.SAL , row_number()over(PARTITION BY A.JOB , A.DEPTNO ORDER BY A.SAL) AS NUM FROM EMP A
)T 
WHERE T.NUM=1
;

SELECT DISTINCT * FROM EMP ;