select a from ( select '1.1' a from dual union all select '1.1.1' a from dual union all select '1.2.1' a from dual union all select '1.101.5' a from dual union all select '1.10.100.20.3' a from dual ) b order by to_number(replace(a,'.','')) desc; 结果: 1.10.100.20.3 1.101.5 1.2.1 1.1.1 1.1不知道是不是楼主想要的结果.
CREATE OR REPLACE FUNCTION dot_lpad ( 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,1,length(v_char_col2-1)); END IF; END; /select instr('luoyoumou','o',1,2) from dual;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');
-- else部分有点小错误,修正一下: CREATE OR REPLACE FUNCTION dot_lpad ( 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;
-- 还有: -- 应该是 IF v_loop > 0 THENCREATE OR REPLACE FUNCTION dot_lpad ( 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 > 0 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; /
-- 完全正确的答案如下:(经测试:还是要用 >2 ,呵呵)CREATE OR REPLACE FUNCTION dot_lpad ( 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_col,v_sign,'')); IF v_loop > 2 THEN -- 表示该字段中至少含有一个 v_sign 字符 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'); SELECT name, dot_lpad(name,'-',4,'0') as name2 FROM test ORDER BY dot_lpad(name,'-',4,'0') desc;
有两个问题: 1.为什么其他排序都对的,唯独“1”排在“1.1”后面呢? 2.“ORDER BY dot_lpad(name,'.',4,'0');”这里面的“4”,“0”什么意思?
---------- 修正一下:----------------- -- 完全正确的答案如下:(经测试:还是要用 >2 ,呵呵)CREATE OR REPLACE FUNCTION dot_lpad ( 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_col,v_sign,'')); IF v_loop > 2 THEN -- 表示该字段中至少含有一个 v_sign 字符 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 lpad(substr(v_char_col2,2,length(v_char_col2)-2),bit_num,fill_str); END IF; END; /----------------------------------------------------------------------------------------------------------- DROP TABLE test;CREATE TABLE test(name VARCHAR2(40));INSERT INTO test(name) VALUES('1'); 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,'.',3,'0') as name2 FROM test ORDER BY dot_lpad(name,'.',3,'0'); SELECT name, dot_lpad(name,'-',5,'0') as name2 FROM test ORDER BY dot_lpad(name,'-',5,'0') desc;
select '1.1' a from dual
union all
select '1.1.1' a from dual
union all
select '1.2.1' a from dual
union all
select '1.101.5' a from dual
union all
select '1.10.100.20.3' a from dual
) b order by to_number(replace(a,'.','')) desc;
结果:
1.10.100.20.3
1.101.5
1.2.1
1.1.1
1.1不知道是不是楼主想要的结果.
http://topic.csdn.net/u/20100516/21/01bf5341-db95-4797-8655-1f058e0881e5.html
能有个oracle函数来处理吗?
我不太会写,能不能麻烦你写一个呢?
谢啦!
(
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,1,length(v_char_col2-1));
END IF;
END;
/select instr('luoyoumou','o',1,2) from dual;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');
CREATE OR REPLACE FUNCTION dot_lpad
(
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;
-- 应该是 IF v_loop > 0 THENCREATE OR REPLACE FUNCTION dot_lpad
(
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 > 0 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;
/
(
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_col,v_sign,''));
IF v_loop > 2 THEN -- 表示该字段中至少含有一个 v_sign 字符
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');
SELECT name,
dot_lpad(name,'-',4,'0') as name2
FROM test
ORDER BY dot_lpad(name,'-',4,'0') desc;
1.为什么其他排序都对的,唯独“1”排在“1.1”后面呢?
2.“ORDER BY dot_lpad(name,'.',4,'0');”这里面的“4”,“0”什么意思?
这样会消耗资源,影响SQL性能。编号最好是每一级等长的,这样直接排序就可以了
---------- 修正一下:-----------------
-- 完全正确的答案如下:(经测试:还是要用 >2 ,呵呵)CREATE OR REPLACE FUNCTION dot_lpad
(
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_col,v_sign,''));
IF v_loop > 2 THEN -- 表示该字段中至少含有一个 v_sign 字符
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 lpad(substr(v_char_col2,2,length(v_char_col2)-2),bit_num,fill_str);
END IF;
END;
/-----------------------------------------------------------------------------------------------------------
DROP TABLE test;CREATE TABLE test(name VARCHAR2(40));INSERT INTO test(name) VALUES('1');
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,'.',3,'0') as name2
FROM test
ORDER BY dot_lpad(name,'.',3,'0');
SELECT name,
dot_lpad(name,'-',5,'0') as name2
FROM test
ORDER BY dot_lpad(name,'-',5,'0') desc;
-- 你的第二个问题,正是我之前问你的问题
-- 最多三位,对吧?
-- 如果两个句点之前的数值宽度最多三位,你就可以用>=3的任意整数(当然最好用3)!
-- 如果两个句点之前的数值宽度最多四位,你就可以用>=4的任意整数(当然最好用4)!
-- 依此类推!