工作偏运维DBA,所以性能优化的sql问题比较多,但功能实现的sql问题比较少.今天遇到一个用户功能实现的sql问题,简单但比较有意思.
贴出来分享给大家.

问题:
假设数据库中存在用户 q, qa, qq, qaq, qaaaaaaaaaa, aq, aqa, aqq, aaq。
在输入搜索框输入一个 q，则显示结果应为：
     (1)q
     (2)qa
     (3)qq
     (4)qaq
     (5)qaaaaaaaaaa
     (6)aq
     (7)aqa
     (8)aqq
     (9)aaq
说明：处理顺序根据数字的从小到大，从先到后：
1.     全文匹配。[(1)排在最前]
2.     结果与输入内容从前向后开始比对，开始相同字符位越靠前，越排在前面。[(2)(3)(4)(5)在(6)(7)(8)前,(6)(7)(8)在(9)前]
3.     看字符相同位后面的字符位数，字符位数少的排前面。[(2)(3)在(4)前，(4)在(5)前，同理(6)在(7)前]
4.     字符位数相同时，看字符对应 ASC 码，小的排前面。[(2)在(3)前，同理(7)在(8)前]

处理:
--产生测试数据
create table scott.testorder
as select 'q' x from dual
union all select 'qa' from dual
union all select 'qq' from dual
union all select 'qaq' from dual
union all select 'qaaaaaaaaaa' from dual
union all select 'aq' from dual
union all select 'aqa' from dual
union all select 'aqq' from dual
union all select 'abq' from dual
union all select 'acq' from dual
union all select 'acqb' from dual;

--查询实现
select x
from scott.testorder
where x like '%q%'
order by
nvl(length(substr(x,0,instr(x,'q')-1)),0),
substr(x,0,instr(x,'q')-1),
nvl(length(substr(x,instr(x,'q')+1,length(x))),0),
substr(x,instr(x,'q')+1,length(x));

/*
X
q
qa
qq
qaq
qaaaaaaaaaa
aq
aqa
aqq
abq
acq
acqb
*/

解释:
--以下查看排序列的值
select x,
nvl(length(substr(x,0,instr(x,'q')-1)),0) "左边有几个字符",
substr(x,0,instr(x,'q')-1) "左边的字符串",
nvl(length(substr(x,instr(x,'q')+1,length(x))),0) "右边有几个字符",
substr(x,instr(x,'q')+1,length(x)) "右边的字符串"
from scott.testorder
where x like '%q%'
order by
nvl(length(substr(x,0,instr(x,'q')-1)),0),
substr(x,0,instr(x,'q')-1),
nvl(length(substr(x,instr(x,'q')+1,length(x))),0),
substr(x,instr(x,'q')+1,length(x));
/*
X    左边有几个字符    左边的字符串    右边有几个字符    右边的字符串
q      0                                     0    
qa    0                                     1             a
qq    0                                     1             q
qaq    0                                     2             aq
qaaaaaaaaaa    0                             10             aaaaaaaaaa
aq    1                  a                   0    
aqa    1                  a                   1             a
aqq    1                  a                   1             q
abq    2                  ab                 0    
acq    2                  ac                 0    
acqb    2                ac                 1             b
*/

把x拆分出4个属性,
order by 中nvl(length(substr(x,0,instr(x,'q')-1)),0)取第一个匹配字符串的左边有几个字符",
substr(x,0,instr(x,'q')-1) 取第一个匹配字符串的左边的字符串,
nvl(length(substr(x,instr(x,'q')+1,length(x))),0) 取第一个匹配字符串的右边有几个字符,
substr(x,instr(x,'q')+1,length(x)) 取第一个匹配字符串的右边的字符串,
然后按这4个属性排序就可得出想要的顺序.