有一个排序,我自己写了一个查询:select t.bjid, t.bjmc from scwl.wl_cpbom t order by to_number(replace(t.bjid,'-',''));其查询结果如下:
ID BJID BJMC
---------------------------------
1 1 孤形扇形段底座部件三
2 1 大部件
3 1 222
4 1-1 支架
5 1-1 支架
6 1-1 锻造件
7 1-2 轴承
8 1-2 轴承
9 1-10 222
10 1-1-1 钢管01我希望的结果入下:
ID BJID BJMC
---------------------------------
1 1 孤形扇形段底座部件三
2 1 大部件
3 1 222
4 1-1 支架
5 1-1 支架
6 1-1 锻造件
7 1-1-1 钢管01
8 1-2 轴承
9 1-2 轴承
10 1-10 222
ID BJID BJMC
---------------------------------
1 1 孤形扇形段底座部件三
2 1 大部件
3 1 222
4 1-1 支架
5 1-1 支架
6 1-1 锻造件
7 1-2 轴承
8 1-2 轴承
9 1-10 222
10 1-1-1 钢管01我希望的结果入下:
ID BJID BJMC
---------------------------------
1 1 孤形扇形段底座部件三
2 1 大部件
3 1 222
4 1-1 支架
5 1-1 支架
6 1-1 锻造件
7 1-1-1 钢管01
8 1-2 轴承
9 1-2 轴承
10 1-10 222
解决方案 »
- ORACLE中数据库和表空间之间的关系!
- pro*c中如何获得 游标变量返回 的descriptor
- 急!怎么将Oracel中全角的数字(nvarchar2)转为int类型的数字。
- 请教高手给推荐一本涉及到oracle存储过程和触发器相关的书籍
- alter database close的问题……9i
- Oracle编程高手请进:问一个关于OraclePackage大小的问题
- 请教增加分区的问题
- ****select查询语句问题,****
- 初用oracle9i在98下如何登陆?用什么名称,我是本机实验
- 送分大行动,请问大家如何在ORACLE9I当中创建方案(数据库)?
- 安装ORACLE9I后如何登录到SQL*PLUS和SQLPLUS worksheet
- select ?
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))
有没有更通用的方法阿!
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 createdSQL>
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
29 rows selected注:如果每节中有大于长度3的数值,只需调整Fun_Digit_Replace(sn,'-',3)函数最后一个参数的大小