Oracle高级分组查询20190111

--USING 推荐这个
SELECT E2.* FROM (SELECT JOB,HIREDATE,ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY HIREDATE DESC) RN FROM EMP
ORDER BY JOB) E2
WHERE E2.JOB IS NOT NULL
AND E2.RN <= 2;

--USING 根据JOB分组,自动以job升序了
SELECT E2.* FROM(SELECT JOB,HIREDATE,ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY HIREDATE DESC) RN FROM EMP) E2
WHERE E2.JOB IS NOT NULL
AND E2.RN <= 2;