此排序语句该如何写?

Question

刚才发的帖子地址为：
http://topic.csdn.net/u/20091110/11/2422c79f-9a20-474f-b0e4-81ab073fdd05.html?seed=158339181&r=61056284#r_61056284测试了一下，各位提供的SQL语句不对。SQL> DESC AA; 
Name Type        Nullable Default Comments 
---- ------------ -------- ------- -------- 
ID  VARCHAR2(10) Y                        
NAME VARCHAR2(20) Y  SQL> SELECT * FROM AA; ID        NAME 
---------- -------------------- 
100        aa 
2          bb 
3          cc 
4          dd 
1a        ii 
1b        jj 
1c        kk 
1e        mm 
10a        xx 
3b        ww 
2c        vv 
1d        oo 希望得到如下结果： ID        NAME 
---------- -------------------- 
2          bb 
3          cc 
4          dd 
100        aa 
1a        ii 
1b        jj 
1c        kk 
1d        oo 
1e        mm 
2c        vv 
3b        ww 
10a        xx 请问SQL语句应该如何写？

Answer 1

排序时与值的长度无关，遵守如下条件：
先按照纯数字排序【从小到大】，再按照剩下的非纯数字排序。本以为order by lpad(id,6,'') 这种方式能够满足，但经过测试是不对的。

Answer 2

order by case when translate(id,'#1234567890','#') is null then 1 else 2 end,
  to_number(regexp_substr(id,'[[:digit:]]+')),id

Answer 3

order by case when translate(id,'#1234567890','#') is null then 1 else 2 end, 
  to_number(regexp_substr(id,'^[[:digit:]]+')),id加个符号

Answer 4

呵呵，我明白你的意思了，你写的那个sql语句运行后，能成功得到我之前想要的结果，不过后来我又添加了一条数据：insert into aa values('asb2','xx');
这时候用你之前的那条语句后，显示的结果就...；不过刚才用了现在你提供的语句【添加‘^’】后，得到的结果就是我想要的，再次感谢你的帮助，多谢！！！

Answer 5

可以分两部分来排序，通过union all来实现：
SELECT *
FROM aa
WHERE TRIM(translate(id, '0123456789', ' ')) IS NULL
ORDER BY id ASC
UNION ALL
SELECT *
FROM aa
WHERE TRIM(translate(id, '0123456789', ' ')) IS NOT NULL
ORDER BY id ASC;

Answer 6

楼上的放在PL/SQL中验证，报告错误：
SQL> SELECT *
  2  FROM aa
  3  WHERE TRIM(translate(id, '0123456789', ' ')) IS NULL
  4  ORDER BY id ASC
  5  UNION ALL
  6  SELECT *
  7  FROM aa
  8  WHERE TRIM(translate(id, '0123456789', ' ')) IS NOT NULL
  9  ORDER BY id ASC
 10  ;
 
SELECT *
FROM aa
WHERE TRIM(translate(id, '0123456789', ' ')) IS NULL
ORDER BY id ASC
UNION ALL
SELECT *
FROM aa
WHERE TRIM(translate(id, '0123456789', ' ')) IS NOT NULL
ORDER BY id ASC
 
ORA-00933: SQL 命令未正确结束
单个可以，但是2个放在一起就报错了。