刚接手这块不久, 今天遇到了个问题, 以前开发的人直接把一个信息全部保存在一个字段里,导致现在用户需要这个字段进行排序,我很难实现, 坐等大神
with data as (
select 'CCC[2007]111号' as book_no from dual union all
select 'CCC[2007]112号' as book_no from dual union all
select 'CCC[2007]22号' as book_no from dual union all
select 'CCC[2007]56号' as book_no from dual union all
select 'AAAAAAA[2007]121号' as book_no from dual union all
select 'AAAAAAA[2007]122号' as book_no from dual union all
select 'AAAAAAA[2007]2号' as book_no from dual union all
select 'AAAAAAA[2008]33号' as book_no from dual union all
select 'BBBB[2007]111号' as book_no from dual union all
select 'BBBB[2007]112号' as book_no from dual union all
select 'BBBB[2008]3号' as book_no from dual union all
select 'BBBB[2008]56号' as book_no from dual
)
就像这个。 把相应的字符串 CCC 或者BBBB 先放一起,然后根据中间的年份进行排序,然后再根据后面的数字进行排序,我这里有一个
select book_no
from data
order by substr (book_no, 1, instr (book_no, ']')),
to_number (
replace (regexp_substr (book_no, '\]([[:digit:]]+)'), ']', ''))但是这个只能把前面相同的 包括了年份放一起, 然后按照后面的数字进行排序, 在升序 降序的时候 中间的年份并没有做任何的操作,, 希望各位大神帮帮忙, 在把前面字符放一起的情况下, 还能按照中间的年份进行排序,最后在按照后面的数字进行排序
AAAAAAA[2008]33号
AAAAAAA[2007]2号
AAAAAAA[2007]121号
AAAAAAA[2007]122号
BBBB[2008]3号
BBBB[2008]56号
BBBB[2007]111号
BBBB[2007]112号
CCC[2007]22号
CCC[2007]56号
CCC[2007]111号
CCC[2007]112号
with data as (
select 'CCC[2007]111号' as book_no from dual union all
select 'CCC[2007]112号' as book_no from dual union all
select 'CCC[2007]22号' as book_no from dual union all
select 'CCC[2007]56号' as book_no from dual union all
select 'AAAAAAA[2007]121号' as book_no from dual union all
select 'AAAAAAA[2007]122号' as book_no from dual union all
select 'AAAAAAA[2007]2号' as book_no from dual union all
select 'AAAAAAA[2008]33号' as book_no from dual union all
select 'BBBB[2007]111号' as book_no from dual union all
select 'BBBB[2007]112号' as book_no from dual union all
select 'BBBB[2008]3号' as book_no from dual union all
select 'BBBB[2008]56号' as book_no from dual
)
就像这个。 把相应的字符串 CCC 或者BBBB 先放一起,然后根据中间的年份进行排序,然后再根据后面的数字进行排序,我这里有一个
select book_no
from data
order by substr (book_no, 1, instr (book_no, ']')),
to_number (
replace (regexp_substr (book_no, '\]([[:digit:]]+)'), ']', ''))但是这个只能把前面相同的 包括了年份放一起, 然后按照后面的数字进行排序, 在升序 降序的时候 中间的年份并没有做任何的操作,, 希望各位大神帮帮忙, 在把前面字符放一起的情况下, 还能按照中间的年份进行排序,最后在按照后面的数字进行排序
AAAAAAA[2008]33号
AAAAAAA[2007]2号
AAAAAAA[2007]121号
AAAAAAA[2007]122号
BBBB[2008]3号
BBBB[2008]56号
BBBB[2007]111号
BBBB[2007]112号
CCC[2007]22号
CCC[2007]56号
CCC[2007]111号
CCC[2007]112号
order by substr(book_no,instr(book_no,'[') + 1,instr(book_no,']')- instr(book_no,'[') - 1)
,to_number(substr(book_no,instr(book_no,']') + 1, instr(book_no,'号') - instr(book_no,']') -1))无测试环境,请测试。
select *
from data
order by regexp_substr(book_no,'[A-Z]+'),regexp_substr(book_no,'[0-9]+')
select *
from data
order by regexp_substr(book_no,'[A-Z]+'),regexp_substr(book_no,'[0-9]+')
,to_number(substr(book_no,instr(book_no,']')+1,instr(book_no,'号')-instr(book_no,']')-1))
book_no
--------------------------------
1 AAAAAAA[2007]2号
2 AAAAAAA[2007]121号
3 AAAAAAA[2007]122号
4 AAAAAAA[2008]33号
5 BBBB[2007]111号
6 BBBB[2007]112号
7 BBBB[2008]3号
8 BBBB[2008]56号
9 CCC[2007]22号
10 CCC[2007]56号
11 CCC[2007]111号
12 CCC[2007]112号
select 'CCC[2007]111号' as book_no from dual union all
select 'CCC[2007]112号' as book_no from dual union all
select 'CCC[2007]22号' as book_no from dual union all
select 'CCC[2007]56号' as book_no from dual union all
select 'AAAAAAA[2007]121号' as book_no from dual union all
select 'AAAAAAA[2007]122号' as book_no from dual union all
select 'AAAAAAA[2007]2号' as book_no from dual union all
select 'AAAAAAA[2008]33号' as book_no from dual union all
select 'BBBB[2007]111号' as book_no from dual union all
select 'BBBB[2007]112号' as book_no from dual union all
select 'BBBB[2008]3号' as book_no from dual union all
select 'BBBB[2008]56号' as book_no from dual
)select book_no, substr(book_no,1,instr(book_no,'[')-1) head,regexp_substr(book_no,'[[:digit:]]+',1,1) yy,
regexp_substr(book_no,'[[:digit:]]+',1,2) ed from t
order by head asc,yy desc,ed asc
with data1 as (
select 'CCC[2007]111号' as book_no from dual union all
select 'CCC[2007]112号' as book_no from dual union all
select 'CCC[2007]22号' as book_no from dual union all
select 'CCC[2007]56号' as book_no from dual union all
select 'AAAAAAA[2007]121号' as book_no from dual union all
select 'AAAAAAA[2007]122号' as book_no from dual union all
select 'AAAAAAA[2007]2号' as book_no from dual union all
select 'AAAAAAA[2008]33号' as book_no from dual union all
select 'BBBB[2007]111号' as book_no from dual union all
select 'BBBB[2007]112号' as book_no from dual union all
select 'BBBB[2008]3号' as book_no from dual union all
select 'BBBB[2008]56号' as book_no from dual UNION ALL
select 'CC[2008]56号' as book_no from dual UNION ALL
select 'CC[2009]56号' as book_no from dual
)SELECT g3 FROM (
SELECT g1,g2,g4,g3 FROM
(
SELECT g.g1,g.g2,g.g4,g.g3,row_number()over(PARTITION BY g.g3 ORDER BY g.g3) rn FROM
(
SELECT a.book_no g1,b.book_no g2,ok.book_no g4,c.book_no g3 FROM (
SELECT substr(book_no,1,instr(book_no,'[')-1) book_no FROM data1) a,(
SELECT to_number(replace(regexp_substr(book_no,'[0-9]+号'),'号')) book_no FROM data1) b,
(
SELECT replace(replace(regexp_substr(book_no,'[[0-9]+]'),'['),']') book_no FROM data1) ok,(
SELECT book_no book_no FROM data1) c
WHERE a.book_no =substr(c.book_no,1,instr(c.book_no,'[')-1)
AND b.book_no= to_number(replace(regexp_substr(c.book_no,'[0-9]+号'),'号'))
AND ok.book_no=replace(replace(regexp_substr(c.book_no,'[[0-9]+]'),'['),']') ) g )h
WHERE h.rn=1
ORDER BY h.g1 ,g4 DESC,g2 )