我为什么是按这样的顺序排: ph1000 ph333 ph888另外,order by to_number(replace(batch_no,'ph'))有问题吧, 报错啊
SQL> ed 已写入文件 afiedt.buf 1 create table tb as select 'ph888' batch_no from dual union 2 select 'ph333' from dual union 3* select 'ph1000' from dual 4 /表已创建。SQL> select * from tb;BATCH_ ------ ph1000 ph333 ph888SQL> select * from tb order by to_number(replace(batch_no,'ph'));BATCH_ ------ ph333 ph888 ph1000小孬,怎么有错?
SQL> select * from test 2 ;AA -------------------- ph333 ph1000 ph888SQL> SQL> Select To_Number(Substr(Aa, 3, Length(Aa) )) 2 From Test 3 Order By To_Number(Substr(Aa, 3, Length(Aa) )) Asc 4 ;TO_NUMBER(SUBSTR(AA,3,LENGTH(A ------------------------------ 333 888 1000SQL>
SQL> select * from test 2 ;AA -------------------- ph333 ph1000 ph888SQL> SQL> Select To_Number(Substr(Aa, 3, Length(Aa) )) 2 From Test 3 Order By To_Number(Substr(Aa, 3, Length(Aa) )) Asc 4 ;TO_NUMBER(SUBSTR(AA,3,LENGTH(A ------------------------------ 333 888 1000SQL>
ph1000
ph333
ph888另外,order by to_number(replace(batch_no,'ph'))有问题吧,
报错啊
已写入文件 afiedt.buf 1 create table tb as select 'ph888' batch_no from dual union
2 select 'ph333' from dual union
3* select 'ph1000' from dual
4 /表已创建。SQL> select * from tb;BATCH_
------
ph1000
ph333
ph888SQL> select * from tb order by to_number(replace(batch_no,'ph'));BATCH_
------
ph333
ph888
ph1000小孬,怎么有错?
2 ;AA
--------------------
ph333
ph1000
ph888SQL>
SQL> Select To_Number(Substr(Aa, 3, Length(Aa) ))
2 From Test
3 Order By To_Number(Substr(Aa, 3, Length(Aa) )) Asc
4 ;TO_NUMBER(SUBSTR(AA,3,LENGTH(A
------------------------------
333
888
1000SQL>
2 ;AA
--------------------
ph333
ph1000
ph888SQL>
SQL> Select To_Number(Substr(Aa, 3, Length(Aa) ))
2 From Test
3 Order By To_Number(Substr(Aa, 3, Length(Aa) )) Asc
4 ;TO_NUMBER(SUBSTR(AA,3,LENGTH(A
------------------------------
333
888
1000SQL>
也许是ah333,.....
也许是330088-2,......
但每次排序时的记录格式必遵循同种规范,
问如何才能按字符序排序,而不是按汉语序
ph333
ph888
这种就是字符序啊,不是什么汉语序
你到底要什么样的结果。把你要遵循的同种规范用数据例举出来。