今天同事遇到下面一个问题：
order by decode(column_id,1,null,2,null,3,null,column_id);
有个问题，就是当列数大于10列时，column_id 的顺序成10，11，12，13，4，5，6，7，8，9了
这个排序的主要目的是让前3列排在后面，这3列的顺序无所谓。
对于小于10列的表是没问题的：
SQL> create table t(c1 number,c2 number,c3 number,c4 number,c5 number);
表已创建。
SQL> col column_name format a20
SQL> select column_name,column_id
2 from user_tab_columns
3 where table_name='T'
4 order by decode(column_id,1,null,2,null,3,null,column_id);
COLUMN_NAME COLUMN_ID
-------------------- ----------
C4 4
C5 5
C2 2
C1 1
C3 3
但是当表的列数大于10的时候就会混乱了。
SQL> select column_name,column_id, decode(column_id,1,null,2,null,3,null,column_id) sortcolumn
2 from user_tab_columns
3 where table_name='T'
4 order by decode(column_id,1,null,2,null,3,null,column_id)
5 /
COLUMN_NAME COLUMN_ID SORTCOLUMN
-------------------- ---------- --------------------
C10 10 10
C11 11 11
C12 12 12
C4 4 4
C5 5 5
C6 6 6
C7 7 7
C8 8 8
C9 9 9
C3 3
C2 2
C1 1
已选择12行。
显然ORACLE把SORTCOLUMN列作为为字符类型排序了。
加个TO_NUMBER即可解决这个问题。
SQL> select column_name,column_id, decode(column_id,1,null,2,null,3,null,column_id) sortcolumn
2 from user_tab_columns
3 where table_name='T'
4 order by to_number(decode(column_id,1,null,2,null,3,null,column_id));
COLUMN_NAME COLUMN_ID SORTCOLUMN
-------------------- ---------- --------------------
C4 4 4
C5 5 5
C6 6 6
C7 7 7
C8 8 8
C9 9 9
C10 10 10
C11 11 11
C12 12 12
C1 1
C3 3
C2 2
已选择12行。
但是为什么会导致这个问题，DECODE函数为何返回了字符类型。
这个问题yangtingkun大师专门写个几篇文章介绍。
有兴趣的可以找找看看。
在这里我借花献佛简单稍微说一下：
对于NULL 类型,ORACLE的默认返回类型是VARCHAR。
对于DECODE函数 ORACLE返回的类型依赖于第一个值。
如下所示：
SQL> CREATE TABLE A AS SELECT DECODE(DUMMY,'X',1,'Y','2',DUMMY) C1, <---由于第一个返回的值1是整数类型，因此整个表达式返回整数类型
2 DECODE(DUMMY,'X','1','Y',2,DUMMY) C2 , <---由于第一个返回的值'1'是字符类型，因此整个表达式返回CHAR类型
3 DECODE(DUMMY,'X',NULL,'Y','HUATENG',DUMMY) C3 FROM DUAL; <---由于第一个返回的值是NULL，因此整个表达式返回CHAR类型
表已创建。
SQL> DESC A
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
C1 NUMBER
C2 VARCHAR2(1)
C3 VARCHAR2(7)
也正是因为DECODE函数的这种依赖于第一次的值类型作为返回类型，对于其他返回的值如果和第一个类型不匹配
，可能会让你遇到很蛋疼的问题：
SQL> DESC A;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
C1 NUMBER
C2 VARCHAR2(1)
C3 VARCHAR2(7)
SQL> INSERT INTO A VALUES(2,2,2);
已创建 1 行。
SQL> SELECT * FROM A;
C1 C2 C3
---------- -- --------------
1 1
2 2 2
SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A;
ERROR:
ORA-01722: 无效数字
未选定行
SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=1;
DECODE(C1,1,1,2,'E',C1)
-----------------------
1
SQL> SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=2;
SELECT DECODE(C1,1,1,2,'E',C1) FROM A WHERE C1=2
*
第 1 行出现错误:
ORA-01722: 无效数字
上面的问题主要是字符'E'无法转为整数类型导致的。
