工作偏運維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個屬性排序就可得出想要的順序.

oracle小知識點4--利用將欄位拆分成多個屬性實現order by複雜排序

相關文章