一个排序问题,不知道用什么方法好！~
有一个排序，我自己写了一个查询：select t.bjid, t.bjmc from scwl.wl_cpbom t order by to_number(replace(t.bjid,'-',''))；其查询结果如下：
ID BJIDBJMC
---------------------------------
11孤形扇形段底座部件三
21大部件
31222
41-1支架
51-1支架
61-1锻造件
71-2轴承
81-2轴承
91-10222
101-1-1钢管01
我希望的结果入下：
ID BJIDBJMC
---------------------------------
11孤形扇形段底座部件三
21大部件
31222
41-1支架
51-1支架
61-1锻造件
71-1-1钢管01
81-2轴承
91-2轴承
101-10222
[解决办法]
分别取出1-1-1三个数字再排序
select bjid, bjid||'--' CC
from
(select '1-1' as bjid from dual
union
select '1-2' as bjid from dual
union
select '1-10-1' as bjid from dual
union
select '1-1-1' as bjid from dual)
order by
to_number(substr(CC,1,instr(CC,'-')-1)),
to_number(substr(CC,instr(CC,'-',1,1)+1,instr(CC,'-',1,2)-instr(CC,'-',1,1)-1)),
to_number(substr(CC,instr(CC,'-',1,2)+1,instr(CC,'-',1,3)-instr(CC,'-',1,2)-1))
[解决办法]
写函数，假设你每个"-"分隔的小节的长度不大于３，总字符串长度小于等于１５０，可以用看下我写的脚本
SQL> create or replace function Fun_Digit_Replace(P_C_InStr varchar2
2 ,P_C_SepStr varchar2
3 ,P_N_LenPerSite number)
4 return varchar2 is
5
6 /*==========================================================================
7
8 功能： 填充指定字符串的每一节到指定长度，不够的在左恻用0补足，并将分隔符去除
9
10 作者： Powerise.GuoYong-Dai
11
12 说明： 此程序仅供个人学习交流，违者也没法追究
13
14 ============================================================================*/
15
16 Result varchar2(150);
17 V_N_Site1 number;
18 V_N_Site2 number;
19 V_C_InStr varchar2(150);
20 V_C_LeftStr varchar2(150);
21 V_C_RightStr varchar2(150);
22 V_C_TempStr varchar2(150);
23 begin
24
25 Result := null;
26 V_C_InStr := P_C_InStr;
27
28 if P_C_InStr is null or length(P_C_InStr) < 1 then
29 return Result;
30 end if;
31
32 if P_C_InStr is null or length(P_C_InStr) < 1 then
33 return P_C_InStr;
34 end if;
35
36 IF P_N_LenPerSite IS NULL OR P_N_LenPerSite < 1 THEN
37 return Result;
38 END IF;
39 dbms_output.put_line(V_C_InStr);
40 dbms_output.put_line(P_N_LenPerSite);
41 LOOP
42 EXIT WHEN INSTR(V_C_InStr,P_C_SepStr) < 1;
43 V_C_LeftStr := substr(V_C_InStr,1,INSTR(V_C_InStr,P_C_SepStr)-1);
44 V_C_TempStr := null;
45 select substr(V_C_InStr
46 ,INSTR(V_C_InStr,P_C_SepStr) + Length(P_C_SepStr)
47 ,Decode( INSTR(V_C_InStr,P_C_SepStr,1,2)
48 ,0
49 ,Length(V_C_InStr) + 1
50 ,INSTR(V_C_InStr,P_C_SepStr,1,2) - INSTR(V_C_InStr,P_C_SepStr) - 1
51 )
52 ) into V_C_TempStr
53 from dual;
54
55
56 V_C_RightStr := null;
57 select substr(V_C_InStr
58 ,Decode( INSTR(V_C_InStr,P_C_SepStr,1,2)
59 ,0
60 ,Length(V_C_InStr) + 1
61 ,INSTR(V_C_InStr,P_C_SepStr,1,2)
62 ) --+ Length(P_C_SepStr)
63 ,Length(V_C_InStr)
64 ) into V_C_RightStr
65 from dual;
66
67 if V_C_TempStr is null or Length(V_C_TempStr) < P_N_LenPerSite then
68 if V_C_TempStr is null then
69 V_C_TempStr := Substr('0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
70 ,1
71 ,P_N_LenPerSite);
72 else
73 V_C_TempStr := Substr('0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
74 ,1
75 ,P_N_LenPerSite - Length(V_C_TempStr)
76 ) || V_C_TempStr;
77 end if;
78 end if;
79
80 if V_C_LeftStr is null then
81 V_C_LeftStr := V_C_TempStr;
82 else
83
84 if Length(V_C_LeftStr) < P_N_LenPerSite then
85 V_C_LeftStr := Substr('0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
86 ,1
87 ,P_N_LenPerSite - Length(V_C_LeftStr)
88 ) || V_C_LeftStr;
89 end if;
90
91 V_C_LeftStr := V_C_LeftStr || V_C_TempStr;
92
93 end if;
94
95 if V_C_RightStr is null then
96 V_C_LeftStr := V_C_LeftStr;
97 else
98 V_C_LeftStr := V_C_LeftStr || V_C_RightStr;
99 end if;
100
101 V_C_InStr := V_C_LeftStr;
102
103 END LOOP;
104
105 if Length(V_C_InStr) < P_N_LenPerSite then
106 V_C_InStr := Substr('0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
107 ,1
108 ,P_N_LenPerSite - Length(V_C_InStr)
109 ) || V_C_InStr;
110 end if;
111
112 return V_C_InStr;
113
114 end Fun_Digit_Replace;
115 /
Function created
SQL>
SQL> select sn from
2 (
3 (select '1' as sn from dual)
4 union all
5 (select '2' as sn from dual)
6 union all
7 (select '1-1' as sn from dual)
8 union all
9 (select '1-1-1' as sn from dual)
10 union all
11 (select '1-2' as sn from dual)
12 union all
13 (select '1-10' as sn from dual)
14 union all
15 (select '1-2-3' as sn from dual)
16 union all
17 (select '1-2-11' as sn from dual)
18 union all
19 (select '1-2-101' as sn from dual)
20 ) a
21 order by Fun_Digit_Replace(sn,'-',3);
SN
-------
1
1-1
1-1-1
1-2
1-2-3
1-2-11
1-2-101
1-10
2
9 rows selected
注：如果每节中有大于长度３的数值，只需调整Fun_Digit_Replace(sn,'-',3)函数最后一个参数的大小