table1中字段CCM为字符串型
CCM
1
1-1
1-1-1
1-1-2
1-10
1-2
1-3
1-3-1
1-3-2
1-3-3
1-4
1-5
1-6
1-7
1-8
1-9
用select ccm from table1 order by ccm 得到的就是上面的结果,而我想要的结果是
1
1-1
1-1-1
1-1-2
1-2
1-3
1-3-1
1-3-2
1-3-3
1-4
1-5
1-6
1-7
1-8
1-9
1-10
怎么才能用一句话能把1-10放到1-9后面啊?或用其他方法解决
CCM
1
1-1
1-1-1
1-1-2
1-10
1-2
1-3
1-3-1
1-3-2
1-3-3
1-4
1-5
1-6
1-7
1-8
1-9
用select ccm from table1 order by ccm 得到的就是上面的结果,而我想要的结果是
1
1-1
1-1-1
1-1-2
1-2
1-3
1-3-1
1-3-2
1-3-3
1-4
1-5
1-6
1-7
1-8
1-9
1-10
怎么才能用一句话能把1-10放到1-9后面啊?或用其他方法解决
nvl(to_number(regexp_substr(CCM, '(\d+)', 1, 2)), 0),
nvl(to_number(regexp_substr(CCM, '(\d+)', 1, 3)), 0)
这里要那样排序的话,需要
order by 后面对ccm 应用substr(),instr(),to_number()..效率高不了
用substr不行啊!
“-”与“-”之间的长度是动态的,可能是三位,也可能是10十位
select ccm from (
select ccm,decode(length(ccm)-length(replace(ccm,'-')),0,ccm||'-0-0',1,ccm||'-0',2,ccm) c from tab_order)
order by to_number(substr(c,1,instr(c,'-',1,1)-1)),
to_number(substr(c,instr(c,'-',1,1)+1,instr(c,'-',1,2)-instr(c,'-',1,1)-1));这个能够满足最多2个 - 号出现的情况,如果有三个 - ,则在decode里面再加一个判断,order by再加一个to_number就是了。
不好意思,我用的是oracle8i,不支持正则
select ccm,decode(length(ccm)-length(replace(ccm,'-')),0,ccm||'-0-0',1,ccm||'-0',2,ccm) c from table1)
order by to_number(substr(c,1,instr(c,'-',1,1)-1)),
to_number(substr(c,instr(c,'-',1,1)+1,instr(c,'-',1,2)-instr(c,'-',1,1)-1)),
to_number(substr(c,instr(c,'-',1,2)+1,length(c)-instr(c,'-',1,2)));
ccm||'-0-0-0' ccmm,
lpad( substr(ccm||'-0-0-0', 1, instr(ccm||'-0-0-0','-')-1), 4, '0' ) as ccm1,
lpad( substr(ccm||'-0-0-0', instr(ccm||'-0-0-0','-',1,1)+1, instr(ccm||'-0-0-0','-',1,2)-instr(ccm||'-0-0-0','-',1,1)-1 ), 4, '0' ) as ccm2,
lpad( substr(ccm||'-0-0-0', instr(ccm||'-0-0-0','-',1,2)+1, instr(ccm||'-0-0-0','-',1,3)-instr(ccm||'-0-0-0','-',1,2)-1 ), 4, '0' ) as ccm3
from table1
ORDER BY lpad( substr(ccm||'-0-0-0', 1, instr(ccm||'-0-0-0','-')-1), 4, '0' ),
lpad( substr(ccm||'-0-0-0', instr(ccm||'-0-0-0','-',1,1)+1, instr(ccm||'-0-0-0','-',1,2)-instr(ccm||'-0-0-0','-',1,1)-1 ), 4, '0' ),
lpad( substr(ccm||'-0-0-0', instr(ccm||'-0-0-0','-',1,2)+1, instr(ccm||'-0-0-0','-',1,3)-instr(ccm||'-0-0-0','-',1,2)-1 ), 4, '0' );---------------------------------------------------------------------------------------------------------------------------------------------------------
CCM CCMM CCM1 CCM2 CCM3
--------- ------------ ---------------- ------------------ --------------------
1 1-0-0-0 0001 0000 0000
1-1 1-1-0-0-0 0001 0001 0000
1-1-1 1-1-1-0-0-0 0001 0001 0001
1-1-2 1-1-2-0-0-0 0001 0001 0002
1-2 1-2-0-0-0 0001 0002 0000
1-3 1-3-0-0-0 0001 0003 0000
1-3-1 1-3-1-0-0-0 0001 0003 0001
1-3-2 1-3-2-0-0-0 0001 0003 0002
1-3-3 1-3-3-0-0-0 0001 0003 0003
1-4 1-4-0-0-0 0001 0004 0000
1-5 1-5-0-0-0 0001 0005 0000
1-6 1-6-0-0-0 0001 0006 0000
1-7 1-7-0-0-0 0001 0007 0000
1-8 1-8-0-0-0 0001 0008 0000
1-9 1-9-0-0-0 0001 0009 0000
1-10 1-10-0-0-0 0001 0010 000016 rows selected.
(
v_char_col VARCHAR2, -- 将要填充的变量(字段)
v_sign VARCHAR2, -- 字段中的字符判断标识(你的例子中是句点(.))
bit_num NUMBER, -- 将要左填充的位数
fill_str VARCHAR2 -- 将要左填充的字符
)
RETURN VARCHAR2
AS
v_char_col2 VARCHAR2(4000);
v_loop NUMBER(4,0);
v_return VARCHAR2(4000);
BEGIN
v_char_col2 := v_sign||v_char_col||v_sign;
v_return := '';
v_loop := length(v_char_col2) - length(replace(v_char_col2,v_sign,''));
IF v_loop > 2 THEN
BEGIN
FOR i IN 1..v_loop LOOP
v_return := v_return||lpad( substr(v_char_col2,instr(v_char_col2,v_sign,1,i)+1,instr(v_char_col2,v_sign,1,i+1)-instr(v_char_col2,v_sign,1,i)-1),bit_num,fill_str);
END LOOP;
RETURN v_return;
END;
ELSE
RETURN substr(v_char_col2,2,length(v_char_col2)-2);
END IF;
END;
/-----------------------------------------------------------------------------------------------------------
DROP TABLE test;CREATE TABLE test(name VARCHAR2(40));INSERT INTO test(name) VALUES('1.1');
INSERT INTO test(name) VALUES('1.1.1');
INSERT INTO test(name) VALUES('1.2.1');
INSERT INTO test(name) VALUES('1.101.5');
INSERT INTO test(name) VALUES('1.10.100.20.3');COMMIT;column name for a15
column name2 for a20
SELECT name,
dot_lpad(name,'.',4,'0') as name2
FROM test
ORDER BY dot_lpad(name,'.',4,'0') desc;
SELECT name,
dot_lpad(name,'-',4,'0') as name2
FROM test
ORDER BY dot_lpad(name,'-',4,'0') desc;