ORACLE数据库UNION集合ORDER BY 和 ROWNUM的使用

  在使用ORACLE 数据库的时候，使用到了UNION集合组合两个查询的数据，并进行排序，在这里使用ORDER BY和ROWNUM的时候遇到点问题，记录一下以供大家参考：

首先是在对整个使用UNION组合好的结果集进行排序的时候，ORDER BY出错了，后来发现，在使用UNION的时候，ORDER BY语句必须写在第二个语句之后才有用，否则就会出错ORA-00933: SQL command not properly ended。

我原句是：

SELECT ADMIN_ID, "COUNT"(ADMIN_ID) FROM "JOB" GROUP BY ADMIN_ID  ORDER BY 2 ASC
UNION SELECT "EMPLOYEEID",0 FROM "ADMIN" A WHERE NOT EXISTS
(SELECT "ADMIN_ID",0 FROM "JOB" J WHERE A.EMPLOYEEID=J.ADMIN_ID GROUP BY "ADMIN_ID")

就报了ORA-00933错误，改成

SELECT ADMIN_ID, "COUNT"(ADMIN_ID) FROM "JOB" GROUP BY ADMIN_ID
UNION SELECT "EMPLOYEEID",0 FROM "ADMIN" A WHERE NOT EXISTS
(SELECT "ADMIN_ID",0 FROM "JOB" J WHERE A.EMPLOYEEID=J.ADMIN_ID GROUP BY "ADMIN_ID")  ORDER BY 2 ASC

就OK了。接下来是取结果集中的第一条，很多人都知道ORACLE的ROWNUM语句，但是加在哪呢？我先是直接在第二的子句前面加：

SELECT ADMIN_ID, "COUNT"(ADMIN_ID) FROM "JOB" GROUP BY ADMIN_ID
UNION SELECT "EMPLOYEEID",0 FROM "ADMIN" A WHERE NOT EXISTS
(SELECT "ADMIN_ID",0 FROM "JOB" J WHERE A.EMPLOYEEID=J.ADMIN_ID GROUP BY "ADMIN_ID") AND ROWNUM = 1 ORDER BY 2 ASC

结果它只对第二个子句取了第一条，而我要的是全部结果集的第一条，后来一想，一个很简单的方法解决：

SELECT ADMIN_ID FROM (SELECT ADMIN_ID, "COUNT"(ADMIN_ID) FROM "JOB" GROUP BY ADMIN_ID
UNION SELECT "EMPLOYEEID",0 FROM "ADMIN" A WHERE NOT EXISTS
(SELECT "ADMIN_ID",0 FROM "JOB" J WHERE A.EMPLOYEEID=J.ADMIN_ID GROUP BY "ADMIN_ID") ORDER BY 2 ASC) WHERE ROWNUM = 1

也就是在外层再嵌套一个select即可。

O了。谢谢。