昵稱排序順序要求
<ol>
<li>大小寫英文字母排序排在前面</li>
<li>中文按拼音排序</li>
<li>數(shù)字,符號以及emoji符號</li>
</ol>
這是我原來的實(shí)現(xiàn)思路:
http://www.reibang.com/p/29243714a9d3
后來發(fā)現(xiàn)原來的思路是有問題的掷贾，參與拼音排序的就只有中文宦棺，英文是沒有參與排序的订晌，原來的效果:
昵稱
Apple
Boy
阿布
寶寶
要實(shí)現(xiàn)的效果是：
昵稱
Apple
阿布
Boy
寶寶
發(fā)現(xiàn)問題以后，我換了一個(gè)思路：
不再使用原來過于復(fù)雜的做法--利用gbk編碼自帶的拼音來排序：
這種方法對于大小寫字母沒有作用零酪，不符合前面提到的需求；自行將要排序的字符，轉(zhuǎn)換成對應(yīng)的字母：
對于需要排在后面的字符轉(zhuǎn)換為 小寫字母 z (小寫字母z比大寫的ascii碼要大)，
例如常用標(biāo)點(diǎn)符號, emoji 表情符號都會轉(zhuǎn)換成 z橄务，然后使用升序方式排序颤陶。
<pre>
select NICKNAME,
-- 按照拼音排序
case
when left(nickname,1)<0x41 then 'z'
when left(nickname,1)>=0x41 and left(nickname,1)<=0x7A then left(nickname,1)
when left(nickname,1)>=0x7b and left(nickname,1)<=0x7e then 'z'
when
left(nickname,1)>=0xf09f9880
then 'z'
ELSE fristPinyin(nickname)
end
as PinYin
from user
order by PinYin asc,raw_charcode desc
</pre>
效果截圖:
SQL語句用到兩個(gè)重要的自定義函數(shù)
轉(zhuǎn)換中文字的首字母拼音 自定義函數(shù)
<pre>
delimiter $$
CREATE FUNCTION fristPinyin(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE V_RETURN VARCHAR(255);
SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,
0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');
RETURN V_RETURN;
END
delimiter ;
</pre>
轉(zhuǎn)換每個(gè)中文字對應(yīng)的拼音 自定義函數(shù)
<pre>
delimiter $$
CREATE FUNCTION pinyin(P_NAME VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE V_COMPARE VARCHAR(255);
DECLARE V_RETURN VARCHAR(255);
DECLARE I INT;
SET I = 1;
SET V_RETURN = '';
while I < LENGTH(P_NAME) do
SET V_COMPARE = SUBSTR(P_NAME, I, 1);
IF (V_COMPARE != '') THEN
#SET V_RETURN = CONCAT(V_RETURN, ',', V_COMPARE);
SET V_RETURN = CONCAT(V_RETURN, fristPinyin(V_COMPARE));
#SET V_RETURN = fristPinyin(V_COMPARE);
END IF;
SET I = I + 1;
end while;
IF (ISNULL(V_RETURN) or V_RETURN = '') THEN
SET V_RETURN = P_NAME;
END IF;
RETURN V_RETURN;
END
delimiter ;
</pre>
3.進(jìn)一步排序:
前面兩點(diǎn)基本上可以實(shí)現(xiàn)了拼音排序,但因?yàn)閷⒁?非中英文字符 排在后面而將這些字符歸類到 小寫字母 z 這一組颗管，所以對于 會跟首字母拼音為z或者首個(gè)英文單詞為z 混在一起。
需要進(jìn)一步排序去劃分滓走。使用權(quán)重規(guī)則實(shí)現(xiàn)垦江，對應(yīng)不同的分組(這里主要指是那些 非中英字符)文字符 的被分到的z 組)分配不同的權(quán)重。
權(quán)重分配:
<pre>
-- 進(jìn)一步排序權(quán)重
case
when left(nickname,1)>=0x41 and left(nickname,1)<=0x7A then 13 -- 字母 第一位
when left(nickname,1)>=0xf09f9880 then 11 -- 表情 第三位
when left(nickname,1)<0x41 then 10
when left(nickname,1)>=0x7b and left(nickname,1)<=0x7e then 10
ELSE 12 -- 其他(主要是中文第二)
end
as weight
</pre>
完整的SQL語句:
<pre>
select nickname,
-- 按照拼音排序
case
when left(nickname,1)<0x41 then 'z'
when left(nickname,1)>=0x41 and left(nickname,1)<=0x7A then left(nickname,1)
when left(nickname,1)>=0x7b and left(nickname,1)<=0x7e then 'z'
when
left(nickname,1)>=0xf09f9880
then 'z'
ELSE fristPinyin(nickname)
end
as PinYin,
-- 進(jìn)一步排序權(quán)重
case
when left(nickname,1)>=0x41 and left(nickname,1)<=0x7A then 13 -- 字母 第一位
when left(nickname,1)>=0xf09f9880 then 11 -- 表情 第三位
when left(nickname,1)<0x41 then 10
when left(nickname,1)>=0x7b and left(nickname,1)<=0x7e then 10
ELSE 12 -- 其他(主要是中文第二)
end
as weight
from user
order by PinYin asc,weight desc
</pre>
效果圖:
就寫到這里,如果發(fā)現(xiàn)問題搅方，請作出指點(diǎn)比吭。
