注:以下所有sql案例均取自"oracle查詢優化改寫技巧與案例"叢書。
emp表的詳細:
查詢所有資訊，
empno ename job mgr hiredate sal comm deptno
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
1110 張三 主管 3322 12-3月 -14 5200 20
1111 李四 銷售 3321 03-11月-15 3400 500 30
1112 王五 銷售 3321 25-4月 -12 4400 800 30
1113 趙二 後勤 3320 30-5月 -11 3450 40
1114 李磊磊 會計 3319 22-12月-15 2500 50
1115 張少麗 銷售 3321 11-3月 -16 3400 1400 30
1116 林建國 主管 3322 22-1月 -16 5700 20
1117 馬富邦 後勤 3320 22-7月 -13 2800 40
1118 沈倩 會計 3319 06-5月 -10 2100 50
已選擇9行。
1.以指定的次序傳回查詢結果
實際提取資料或生産報表時，一般都要根據一定的順序檢視，比如，想檢視機關所雇員工的資訊。
empno ename hiredate
---------- -------------------- --------------
1110 張三 12-3月 -14
1116 林建國 22-1月 -16
上面除了“order by hiredate asc”的寫法外，還可以寫成“order by 3 asc”，意思是按第三列排序。
當取值不定時，用這種方法就很友善，比如，有時取sal，有時要取comm來顯示:
empno ename sal
---------- -------------------- ----------
1110 張三 5200
1116 林建國 5700
empno ename comm
1111 李四 500
1112 王五 800
1115 張少麗 1400
注意:用資料來代替列位置隻能用于order by子句中，其他地方都不能用。
2.按多個字段排序
要求:按部門編号升序，并按工資降序排列
排序時有兩個關鍵字：asc表示升序，desc表示降序
empno deptno sal ename job
---------- ---------- ---------- -------------------- ------------------
1116 20 5700 林建國 主管
1110 20 5200 張三 主管
1112 30 4400 王五 銷售
1111 30 3400 李四 銷售
1115 30 3400 張少麗 銷售
1113 40 3450 趙二 後勤
1117 40 2800 馬富邦 後勤
1114 50 2500 李磊磊 會計
1118 50 2100 沈倩 會計
對于重複值的排序，原理就是把資料分成了幾組，然後每組的資料再去排序。
3.按子串排序
使用員工号尾号(最後兩位)速查員工可以很快查到，當然也可以使用縮減的子串進行排序。
員工号尾号 deptno sal ename job
---------------- ---------- ---------- -------------------- ------------------
10 20 5200 張三 主管
11 30 3400 李四 銷售
12 30 4400 王五 銷售
13 40 3450 趙二 後勤
14 50 2500 李磊磊 會計
注:substr(expr,number)是字元串截取函數
4.字元串一一替換函數translate
文法格式：translate(expr,from_string,to_string)
示例：
new_str
----------------------------------
12 你好 2314567
from_string與to_string以字元為機關，對應字元一一替換。
如果to_string對應的位置沒有字元，from_string中列出的字元也将會被消掉。
----------------
你好
原因
5.按數字和字母混合字元串中的字母排序
首先建立view如下:
data
1110 張三
1111 李四
1112 王五
1113 趙二
1114 李磊磊
1115 張少麗
1116 林建國
1117 馬富邦
1118 沈倩
現在隻有一個字段data，裡面就是數字+空格+字母的組合，要求我們用data中的字母(也就是原來的ename)排序。
data ename
------------- -------------
1117 馬富邦 馬富邦
1113 趙二 趙二
1112 王五 王五
1118 沈倩 沈倩
1116 林建國 林建國
1114 李磊磊 李磊磊
1111 李四 李四
1115 張少麗 張少麗
1110 張三 張三
6.處理排序空值
oracle預設排序空值在後面，想把空值放前，以前的做法是nvl(comm,-1)
ename sal comm order_col
-------------------- ---------- ---------- ----------
張三 5200 -1
李磊磊 2500 -1
馬富邦 2800 -1
林建國 5700 -1
趙二 3450 -1
沈倩 2100 -1
李四 3400 500 500
王五 4400 800 800
張少麗 3400 1400 1400
nvl(comm,-1)這種做法需要對列類型及其中儲存的資料有所了解才行，而且儲存的資料如果有變化，該語句就要重新維護。
其實可以用關鍵字nulls first和nulls last。
(1)空值在前
ename sal comm
-------------------- ---------- ----------
張三 5200
李磊磊 2500
馬富邦 2800
林建國 5700
趙二 3450
沈倩 2100
李四 3400 500
王五 4400 800
張少麗 3400 1400
(2)空值在後
7.根據條件取不同列中的值來排序
有時排序的要求會比較複雜，比如：上司對工資在2000到3000元之間的員工更感興趣，于是要求工資在這個範圍的員工要排在前面，便于優先檢視。
我們可以在查詢中新生成一列，用多列排序的方法處理：
編碼 姓名 級别 工資
---------- -------------------- ---------- ----------
1118 沈倩 1 2100
1114 李磊磊 1 2500
1117 馬富邦 1 2800
1115 張少麗 2 3400
1111 李四 2 3400
1113 趙二 2 3450
1112 王五 2 4400
1110 張三 2 5200
1116 林建國 2 5700
也可以不顯示級别，直接把case when 放在 order by中:
編碼 姓名 工資
1118 沈倩 2100
1114 李磊磊 2500
1117 馬富邦 2800
1115 張少麗 3400
1111 李四 3400
1113 趙二 3450
1112 王五 4400
轉載請注明出處：http://blog.csdn.net/acmman/article/details/51038665