Oracle--排序
1.排序有升序和降序之分:
ASC表示升序排序,DESC表示降序排序.如果不指明排序順序,默認的排序順序為升序ASC.
2.對排序字段中包含null值的處理:
nulls first: null值排在最前面;
select name, chinese from zj_test order by chinese nulls first;
nulls last: null值排在最后面;
select name, chinese from zj_test order by chinese nulls last;
注意:是nulls first和nulls last,不是null
3.以表中某些字段的加減乘除作為排序標準:
select name, (chinese + math) as score from zj_test order by score;
4.decode()函數的使用
-- 通過decode()函數，sex值為男的記錄被轉換為了2,而其他的記錄都是1 select name, sex from zj_test order by decode (sex, '男', 2, 1);
5.在order by中使用case語句
case語句分為兩種Case Simple Expression和Case Search Expression:
Case Simple Expression:
1 CASE Column1 2 WHEN V1 THEN R1 3 WHEN V2 THEN R2 4 ELSE R3 5 END
Case Search Expression:
1 CASE 2 WHEN C1=V1 THEN R1 3 WHEN C2=V2 THEN R2 4 ELSE R3 5 END
范例:
-- between...and...前后都是閉區(qū)間
1 select 2 grade, sex, name, chinese 3 from 4 zj_test 5 order by 6 case grade 7 when '一年級' then 1 8 when '二年級' then 2 9 when '三年級' then 3 end, 10 case 11 when sex = '女' then 1 12 else 2 end, 13 case 14 when chinese >= 90 then 1 15 when chinese between 80 and 89 then 2 16 else 3 end;
6.order by 數字
-- 本例中表示,將按照select后面查詢內容的第2列排序，即chinese，如果數字為0或超出查詢出的列數就會報錯。 select name, chinese from zj_test order by 2;
但是,如何chinese字段為varchar2類型,需要使用to_number(將varchar2類型轉為數字)時,這種寫法將無效;
select name, chinese from zj_test order by to_number(2);
(排序錯誤)
select name, chinese from zj_test order by to_number(chinese);
(排序正確)
浙公網安備 33010602011771號