有一个表TB_STDITAL
里面有一列SITEM, varchar类型。
存的东西为条款,数据为:
1.1
1.1.2
1.1.3
1.2
1.2.1
1.3
现在有些条款前带字母,
C.1.2.2
需求:字母C和点. 不参与排序,要求C.1.2.2排在1.3前,而且要排在1.2.1后。而且这个C.两个字符还必须显示在条款上。急啊,流泪求助!!!
里面有一列SITEM, varchar类型。
存的东西为条款,数据为:
1.1
1.1.2
1.1.3
1.2
1.2.1
1.3
现在有些条款前带字母,
C.1.2.2
需求:字母C和点. 不参与排序,要求C.1.2.2排在1.3前,而且要排在1.2.1后。而且这个C.两个字符还必须显示在条款上。急啊,流泪求助!!!
case when substr(SITEM,1,1) between '0' and '9' then SITEM else substr(SITEM,3) end
(
select '1.1' chp from dual union all
select '1.1.2' chp from dual union all
select '1.1.3' chp from dual union all
select '1.2' chp from dual union all
select '1.2.1' chp from dual union all
select '1.3' chp from dual union all
select 'C.1.2.2' chp from dual
)
select chp,regexp_substr(chp, '([0-9]+\.?)+')
from tmp
order by regexp_substr(chp, '([0-9]+\.?)+');CHP REGEXP_SUBSTR(CHP,'([0-9]+\.?)+')
---------- ---------------------------------
1.1 1.1
1.1.2 1.1.2
1.1.3 1.1.3
1.2 1.2
1.2.1 1.2.1
C.1.2.2 1.2.2
1.3 1.3
create table tb_stdital
(sitem varchar(10));insert into tb_stdital values('C.1.1');
insert into tb_stdital values('C.1.2');
insert into tb_stdital values('C.1.3');
insert into tb_stdital values('C.1.2');
insert into tb_stdital values('C.1.2.1');
insert into tb_stdital values('C.1.2.2');
insert into tb_stdital values('C.1.2.1');
insert into tb_stdital values('C.1.3');
insert into tb_stdital values('C.1.3.1');SELECT * FROM tb_stdital ORDER BY sitem ASC;
--查询结果
SITEM
C.1.1
C.1.2
C.1.2
C.1.2.1
C.1.2.1
C.1.2.2
C.1.3
C.1.3
C.1.3.1
order by case when substr(SITEM,1,1) ='C' then substr(SITEM,3)else SITEM end
order by case when substr(SITEM,1,1) ='C' then substr(SITEM,3)else SITEM end