oracle order by 的用法
oracle order by 的用法
今天分享几个order by的用法。
--创建测试表
create table ffy_test(id number, val varchar2(10));
insert into ffy_test(id,val)values(1,'test1');
insert into ffy_test(id,val)values(2,'test2');
insert into ffy_test(id,val)values(3,'test3');
insert into ffy_test(id,val)values(4,'test4');
insert into ffy_test(id,val)values(5,'test5');
insert into ffy_test(id,val)values(6,'test6');
insert into ffy_test(id,val)values(7,'test7');
insert into ffy_test(id,val)values(8,'test8');
insert into ffy_test(id,val)values(9,'test9');
insert into ffy_test(id,val)values(10,'test10');
insert into ffy_test(id,val)values(11,'test11');
insert into ffy_test(id,val)values(12,'test12');
insert into ffy_test(id,val)values(13,'test13');
insert into ffy_test(id,val)values(14,'test14');
insert into ffy_test(id,val)values(15,'test15');
insert into ffy_test(id,val)values(16,'test16');
insert into ffy_test(id,val)values(17,'test17');
insert into ffy_test(id,val)values(18,'test18');
insert into ffy_test(id,val)values(19,'test19');
insert into ffy_test(val)values('test20');
commit;
1. ORDER BY 对NULL的处理。
与sqlserver不同的是，oracle在order by时，认为null是最大值，所以默认排序(ASC)时，会将null放在最后，降序(DESC)时则将null放在最前。而sqlserver截然相反。
当然，你可以使用nulls last / nulls first参数来显式指定null值的顺序。例如：
SQL> select * from ffy_test order by id nulls first;
ID VAL
---------- --------------------
test20
1 test1
2 test2
或者
SQL> select * from ffy_test order by id nulls last;
ID VAL
---------- --------------------
18 test18
19 test19
test20
这些控制null值的参数是忽视asc或desc的，可以理解为，oracle对数据进行asc/desc排序后，再决定通过nulls参数，将null值放在指定位置。例如：
SQL> select * from ffy_test order by id desc nulls last;
ID VAL
---------- --------------------
8 test8
7 test7
6 test6
5 test5
4 test4
3 test3
2 test2
1 test1
test20
2. 几种排序的写法
单列升序：select
单列降序：select
多列升序：select
多列降序：select
多列混合排序：select
3. 指定排序的写法
其实，前两条并不是我最想说的，我最想说的是第三条。你有没有遇到过一些特殊的需求，如对某个字段<10的
值顺序排列,>10的值倒序排列？或将某个值指定排在某一位？真的没有遇到吗？OK，但我有。
ORACLE支持在ORDER BY后面进行DECODE或CASE WHEN进行转换。例如：
/*********************eg:1************************/
SQL> select * from ffy_test
2 order by case when id <= 10 then id
3 when id > 10 then -id
4 end;
ID VAL
---------- --------------------
19 test19
18 test18
17 test17
16 test16
15 test15
14 test14
13 test13
12 test12
11 test11
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
7 test7
8 test8
9 test9
10 test10
test20
20 rows selected.
/*********************eg:2************************/
SQL> select * from ffy_test order by
2 case when id <= 10 then id end asc,
3 case when id > 10 then id end desc;
ID VAL
---------- --------------------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
7 test7
8 test8
9 test9
10 test10
test20
19 test19
18 test18
17 test17
16 test16
15 test15
14 test14
13 test13
12 test12
11 test11
20 rows selected.
/*********************eg:3************************/
SQL> select * from ffy_test order by
2 decode(sign(10-id),1,id,0,id,99999-id);
ID VAL
---------- --------------------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
7 test7
8 test8
9 test9
10 test10
19 test19
18 test18
17 test17
16 test16
15 test15
14 test14
13 test13
12 test12
11 test11
test20
20 rows selected.
上面有三种写法都实现了指定排序，但细心的你发现，eg1和eg3是差不多的，他们都是升序排列，将null视为最大值放在最后，为什么结果不一样哦？问的好，eg1将>10的记录都转换成负值，如果顺序排列负值，当然负值越大排在越前面，因此会形成-19、-18...-11、1、2...null的序列；而eg3使用了sign(10-id)函数，如果值<10，得到的值为1,>10得到的值为-1，=10得到的值为0，通过decode，将1和0使用了原本的id进行顺序排列，-1则使用了99999-id的倒序排列。因此会形成1、2、3...10、(99999-19=99980[19])、(99999-18=99981[18])...(99999-11=99988[11])...null的序列。
OK，eg2中，将数据分为3组，对没错，是3组。<=10的按照id进行升序排列,>10的按照id进行倒序排列，还有一组是除此之外的，他会紧跟着第一组放在其后，所以null是最大值，却被放在<=10的后面。
最后总结一下，使用order by decode/case when时，实际上是将其转义后，对转义后的值进行排序。注意转义后，字段类型可能发生改变。