快捷導(dǎo)航

MySL實(shí)現(xiàn)如等級(jí)成色等特殊順序的排序詳解

更新時(shí)間：2023年05月25日 14:54:46 作者：北橋蘇

這篇文章主要為大家介紹了MySL實(shí)現(xiàn)如等級(jí)成色等特殊順序的排序詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

前言：

在涉及商品類(lèi)的項(xiàng)目時(shí)，為了給同一類(lèi)商品定位，往往會(huì)分等級(jí)或者成色。而等級(jí)/成色有時(shí)是用類(lèi)似A，A+，A+1，K，L1，L2等英文與數(shù)字的組合進(jìn)行表述。既然是等級(jí)，自然就會(huì)區(qū)分高等級(jí)，低等級(jí)。有高有低，又給商品定了等級(jí)，所以到了商品列表展示，等級(jí)排序自然少不了。

項(xiàng)目在開(kāi)始做的時(shí)候關(guān)于商品等級(jí)字段是直接存儲(chǔ)的上面的字母數(shù)字，也就是A，A+1之類(lèi)的。所以在需要以等級(jí)排序的時(shí)候，以英文字母的方式升序倒序就出現(xiàn)了問(wèn)題，比如大部分等級(jí)中，S是最高級(jí)，而英文倒序S排在了后面。

沖突：

直接以英文字母排序很顯然是不行的，于是首先想到的是數(shù)據(jù)表的等級(jí)字段改為int，存入數(shù)字。在配置里或者單獨(dú)建一張表與對(duì)應(yīng)數(shù)字關(guān)聯(lián)一個(gè)等級(jí)。類(lèi)似K對(duì)應(yīng)1之類(lèi)的，需要升序降序就對(duì)數(shù)字排序，這樣就解決了S排序問(wèn)題。但是，現(xiàn)實(shí)的是商品表的數(shù)據(jù)量極大，如果要給原來(lái)的等級(jí)做關(guān)聯(lián)，就要小心的寫(xiě)SQL替換或者手動(dòng)一個(gè)一個(gè)改。不知道有沒(méi)有在不需要修改表的情況下解決那種特殊字符排序的方法，后來(lái)還真想到了一個(gè)方法。

解決方法：

通過(guò)使用MySQL的find_in_set函數(shù)，通過(guò)自定義的順序進(jìn)行排序，所以后續(xù)將某個(gè)等級(jí)提前改動(dòng)都不會(huì)那么被動(dòng)了，如下示例。

1. 可以先定義升序，倒序的排列

"grade_desc" => ["S","A+","A1","A2","B+1","B+2","B1","B2","C+1","C+2","C1","C2","D+1","D+2","D1","D2","D3","E+1","E+2","E1","E2","F","G+","G1","G2","H+","H1","H2","I+","I1","I2","J+","J","K+","K"]"grade_asc" => ["K","K+","J","J+","I2","I1","I+","H2","H1","H+","G2","G1","G+","F","E2","E1","E+2","E+1","D3","D2","D1","D+2","D+1","C2","C1","C+2","C+1","B2","B1","B+2","B+1","A2","A1","A+","S"]

2. 查詢(xún)示例

3. ThinkPHP5的使用方式

3.1. 升序

$list = GoodsItem::where('delete_time','=',0)
            ->field('id,grade')
            ->orderRaw("find_in_set(grade,'K,K+,J,J+,I2,I1,I+,H2,H1,H+,G2,G1,G+,F,E2,E1,E+2,E+1,D3,D2,D1,D+2,D+1,C2,C1,C+2,C+1,B2,B1,B+2,B+1,A2,A1,A+,S')")
            ->select();
return $list->toArray();

3.2. 倒序

$list = GoodsItem::where('delete_time','=',0)
            ->field('id,grade')
            ->orderRaw("find_in_set(grade,'S,A+,A1,A2,B+1,B+2,B1,B2,C+1,C+2,C1,C2,D+1,D+2,D1,D2,D3,E+1,E+2,E1,E2,F,G+,G1,G2,H+,H1,H2,I+,I1,I2,J+,J,K+,K')")
            ->select();
        return $list->toArray();

3.3.效果