我这边有一个文档 编号 是按照下列顺序排列
F_ORDER2.5.1.1(1)
。
2.5.1.1(6)
2.5.1.1(7)
2.5.1.1(8)
2.5.1.1(9)
2.5.1.1(10)但是我order by 这个字段之后
排列的顺序是2.5.1.1(1)
2.5.1.1(10)
。
2.5.1.1(6)
2.5.1.1(7)
2.5.1.1(8)
2.5.1.1(9)请问有什么办法给他弄成正常的顺序啊
F_ORDER2.5.1.1(1)
。
2.5.1.1(6)
2.5.1.1(7)
2.5.1.1(8)
2.5.1.1(9)
2.5.1.1(10)但是我order by 这个字段之后
排列的顺序是2.5.1.1(1)
2.5.1.1(10)
。
2.5.1.1(6)
2.5.1.1(7)
2.5.1.1(8)
2.5.1.1(9)请问有什么办法给他弄成正常的顺序啊
asselect '2.5.1.1(1)' col from dual
union all
select '2.5.1.1(10)' col from dual
union all
select '2.5.1.1(2)' col from dual
union all
select '2.5.1.1(3)' col from dual
union all
select '2.5.2.1(2)' from dual
union all
select '2.5.3.1(1)' from dual ;
select a.col
from a
order by substr(col, 1, instr(col, '(') - 1),
to_number(substr(col,
instr(col, '(') + 1,
instr(col, ')') - instr(col, '(') - 1))
F_ORDER2.5.1.1.1
。
2.5.1.1.6
2.5.1.1.7
2.5.1.1.8
2.5.1.1.9
2.5.1.1.10但是我order by 这个字段之后
排列的顺序是2.5.1.1.1)
2.5.1.1.10)
。
2.5.1.1.6
2.5.1.1.7
2.5.1.1.8
2.5.1.1.9还有这么一种情况
怎么能两种一起解决呢
1 2.5.1.1.1 1
2 2.5.1.1.10 10
3 2.5.1.1.11 11
4 2.5.1.1.12 12
5 2.5.1.1.13 13
6 2.5.1.1.14 14
7 2.5.1.1.15 15
8 2.5.1.1.16 16
9 2.5.1.1.17 17
10 2.5.1.1.18 18
11 2.5.1.1.2 2
12 2.5.1.1.3 3
13 2.5.1.1.4 4
14 2.5.1.1.5 5
15 2.5.1.1.6 6
16 2.5.1.1.7 7
17 2.5.1.1.8 8
18 2.5.1.1.9 9select seq,item from test t order by seq
直接按照seq进行排序就OK了,只要你的SEQ栏位是varchar2类型就行了!测试已经通过.
select t.*, t.rowid from test t order by to_number(substr(seq,9,5))
,LENGTH(A.STRING_NO)
FROM TEST01.STRING_SROT A
GROUP BY LENGTH(A.STRING_NO)
,A.STRING_NO
ORDER BY LENGTH(A.STRING_NO)
,A.STRING_NO
看你数据了,很容易产生隐性BUG的。慎重!
,SUBSTR(A.STRING_NO, 0, INSTR(A.STRING_NO, '.', -1) )
,LENGTH(A.STRING_NO)
FROM TEST01.STRING_SROT A
GROUP BY SUBSTR(A.STRING_NO, 0, INSTR(A.STRING_NO, '.', -1) )
,LENGTH(A.STRING_NO)
,A.STRING_NO
ORDER BY SUBSTR(A.STRING_NO, 0, INSTR(A.STRING_NO, '.', -1) )
,LENGTH(A.STRING_NO)
,A.STRING_NO
我建议你,做一个FUNCTION,这家伙就做把1.2.3.1这种数据变成001002003001这种,转换成数字也不太好
就是每碰上《。》就分开。
要是这组有()这种符号就是0XX(XX是实际值)
没有()的就变成1XX,这样排序的话应该可以
EX)
2.1.1.1->002001001001
2.1.1.(1)->0020010011001这样再排序就没问题了。
这个FUNCTION应该很容易写吧。
to_number(substr(f_order,instr(f_order,'.',1,1)+1,instr(f_order,'.',1,2)-instr(f_order,'.',1,1)-1)),
to_number(substr(f_order,instr(f_order,'.',1,2)+1,instr(f_order,'.',1,3)-instr(f_order,'.',1,2)-1)),
to_number(substr(f_order,instr(f_order,'.',1,3)+1,instr(f_order,'(',1,1)-instr(f_order,'.',1,3)-1)),
to_number(substr(f_order,instr(f_order,'(',1,1)+1,instr(f_order,')',1,1)-instr(f_order,'(',1,1)-1))
下面是转换的方法CREATE OR REPLACE FUNCTION TEST01.REPLACE_STR(V_ORDER_NO IN VARCHAR2) /* (引数, 引数...) */
RETURN VARCHAR2-- 戻り値のデータ型
IS
RETURN_ORDER_NO VARCHAR2(20);
INDX PLS_INTEGER;
V_TEST PLS_INTEGER :=INSTR(V_ORDER_NO,'.',1,1);
V_TEST1 PLS_INTEGER :=INSTR(V_ORDER_NO,'.',1,2);
V_ORDER_NO1 VARCHAR2(5):=SUBSTR(V_ORDER_NO,0,INSTR(V_ORDER_NO,'.')-1);
V_ORDER_NO2 VARCHAR2(5):=SUBSTR(V_ORDER_NO,INSTR(V_ORDER_NO,'.',1,1)+1,INSTR(V_ORDER_NO,'.',1,2)-INSTR(V_ORDER_NO,'.',1,1)-1);
V_ORDER_NO3 VARCHAR2(5):=SUBSTR(V_ORDER_NO,INSTR(V_ORDER_NO,'.',1,2)+1,INSTR(V_ORDER_NO,'.',1,3)-INSTR(V_ORDER_NO,'.',1,2)-1);
V_ORDER_NO4 VARCHAR2(5):=SUBSTR(V_ORDER_NO,INSTR(V_ORDER_NO,'.',1,3)+1,LENGTH(V_ORDER_NO)-INSTR(V_ORDER_NO,'.',1,3));BEGIN
if INSTR(V_ORDER_NO1,'(')=0 THEN
V_ORDER_NO1:=LPAD(V_ORDER_NO1,5,'0');
ELSE
V_ORDER_NO1:=REPLACE(V_ORDER_NO1,'(');
V_ORDER_NO1:=REPLACE(V_ORDER_NO1,')');
V_ORDER_NO1:='1' || LPAD(V_ORDER_NO1,4,'0');
END IF;if INSTR(V_ORDER_NO2,'(')=0 THEN
V_ORDER_NO2:=LPAD(V_ORDER_NO2,5,'0');
ELSE
V_ORDER_NO2:=REPLACE(V_ORDER_NO2,'(');
V_ORDER_NO2:=REPLACE(V_ORDER_NO2,')');
V_ORDER_NO2:='1' || LPAD(V_ORDER_NO2,4,'0');
END IF;if INSTR(V_ORDER_NO3,'(')=0 THEN
V_ORDER_NO3:=LPAD(V_ORDER_NO3,5,'0');
ELSE
V_ORDER_NO3:=REPLACE(V_ORDER_NO3,'(');
V_ORDER_NO3:=REPLACE(V_ORDER_NO3,')');
V_ORDER_NO3:='1' || LPAD(V_ORDER_NO3,4,'0');
END IF;if INSTR(V_ORDER_NO4,'(')=0 THEN
V_ORDER_NO4:=LPAD(V_ORDER_NO4,5,'0');
ELSE
V_ORDER_NO4:=REPLACE(V_ORDER_NO4,'(');
V_ORDER_NO4:=REPLACE(V_ORDER_NO4,')');
V_ORDER_NO4:='1' || LPAD(V_ORDER_NO4,4,'0');
END IF;
RETURN_ORDER_NO:=V_ORDER_NO1 || V_ORDER_NO2 || V_ORDER_NO3 || V_ORDER_NO4 ;
RETURN RETURN_ORDER_NO;
END ;
/用法
[code=SQL]
SELECT A.STRING_NO
FROM TEST01.STRING_SROT A
ORDER BY TEST01.REPLACE_STR (A.STRING_NO)
[/code]