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了。謝謝。