MySQL實(shí)現(xiàn)字段的自定義排序的方法
數(shù)據(jù)
首先有一張產(chǎn)品表
CREATE TABLE `product` ( `product_id` varchar(50) COLLATE utf8mb4_bin NOT NULL, `brand_id` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `brand` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL, `product_type` varchar(3) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'S-Single Item, K-Product Kit', `name_en` varchar(200) COLLATE utf8mb4_bin NOT NULL, `name_t_ch` varchar(240) COLLATE utf8mb4_bin DEFAULT NULL, `name_s_ch` varchar(240) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1702 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
一些數(shù)據(jù):
具體的數(shù)據(jù)可以自己插入一些測(cè)試數(shù)據(jù)進(jìn)行測(cè)試,我的數(shù)據(jù)里面已經(jīng)是有了很多各種數(shù)據(jù),包括了下面排序要求的數(shù)據(jù)了。
需求
最近遇到一個(gè)這樣一個(gè)需求,針對(duì)頁(yè)面上的展示效果需要一個(gè)特定的排序,即我想看到這些特定的產(chǎn)品id需要排在前面,然后后面按照我需要的順序進(jìn)行排序。
之前的排序順序是按照product id的升序排序的,字符串的排序。
SELECT product_id, name_t_ch FROM product p ORDER BY product_id ;
下面則是要求修改后的排序順序:
解決方案
使用代碼實(shí)現(xiàn)排序(不是很好)
因?yàn)檫@個(gè)是在頁(yè)面上有顯示,并且是有分頁(yè),如果使用代碼排序的話,則需要查出所有數(shù)據(jù)然后再手動(dòng)分頁(yè),用不上數(shù)據(jù)庫(kù)的分頁(yè)了。
使用MySQL的排序來(lái)實(shí)現(xiàn)(推薦)
使用 order by field(column, str1,str2,str3)
格式:field(value,str1,str2,str3,str4),value與str1、str2、str3、str4比較,返回1、2、3、 4,如遇到null或者不在列表中的數(shù)據(jù)則返回0
簡(jiǎn)單版本的一個(gè)排序:
SELECT product_id , name_t_ch FROM product p ORDER BY field(product_id, 'MR-0800004', 'MR-0800007', 'MR-0800001', 'MR-0800002') , product_id ;
結(jié)果
第一個(gè)sql版本:
-- 初始版本 SELECT product_id, name_t_ch FROM product p ORDER BY FIELD( CASE WHEN product_id LIKE 'MR-%' THEN 'MR' WHEN product_id LIKE 'SD-%' THEN 'SD' WHEN product_id LIKE 'WN-%' THEN 'WN' WHEN product_id LIKE 'HHC-%' THEN 'HHC' WHEN product_id LIKE 'HS-%' THEN 'HS' WHEN product_id LIKE 'G-%' THEN 'G' ELSE 'OTHER' END, 'MR', 'SD', 'WN', 'HHC', 'HS', 'G', 'OTHER', product_id );
排序結(jié)果:
第二個(gè)sql 版本:
-- 改造版本 SELECT product_id, name_t_ch FROM product p ORDER BY FIELD( CASE WHEN product_id = 'MP-0800004' THEN 'MP-0800004' WHEN product_id = 'MP-0800007' THEN 'MP-0800007' WHEN product_id = 'MR-0800001' THEN 'MR-0800001' WHEN product_id = 'MR-0800002' THEN 'MR-0800002' WHEN product_id = 'MR-0800004' THEN 'MR-0800004' WHEN product_id = 'SD-0800001' THEN 'SD-0800001' WHEN product_id LIKE 'WN-%' THEN 'WN' WHEN product_id LIKE 'HHC-%' THEN 'HHC' WHEN product_id LIKE 'HS-%' THEN 'HS' WHEN product_id LIKE 'G-%' THEN 'G' ELSE 'OTHER' END, 'MP-0800004', 'MP-0800007', 'MR-0800001', 'MR-0800002', 'MR-0800004', 'SD-0800001', 'WN', 'HHC', 'HS', 'G', 'OTHER', product_id );
結(jié)果:
能夠看到排在前面的幾個(gè) product_id 是按照我們需要的順序排序了的。
gpt解釋:在這里在 FIELD
函數(shù)中,第一個(gè)參數(shù)指定了要排序的字段或表達(dá)式,而后續(xù)的參數(shù)指定了排序的順序。在您提供的查詢中,CASE WHEN
表達(dá)式返回的結(jié)果是字符串,這些字符串用于指定排序順序。因此,product_id
不能直接寫在 FIELD
函數(shù)的第一個(gè)參數(shù)中。
使用case when 轉(zhuǎn)換實(shí)現(xiàn)自定義排序
SELECT product_id , name_t_ch FROM product p ORDER BY CASE WHEN product_id LIKE 'MR-%' THEN 1 WHEN product_id LIKE 'SD-%' THEN 2 WHEN product_id LIKE 'WN-%' THEN 3 WHEN product_id LIKE 'HHC-%' THEN 4 WHEN product_id LIKE 'HS-%' THEN 5 WHEN product_id LIKE 'G-%' THEN 6 ELSE 7 -- 其他情況 END, product_id;
排序結(jié)果,按照了我們指定的順序進(jìn)行排序
這個(gè)查詢首先根據(jù) productid
是否以特定前綴開(kāi)頭進(jìn)行分類,并為每個(gè)分類分配一個(gè)排序權(quán)重。然后,按照這個(gè)排序權(quán)重進(jìn)行排序。如果 productid
不以任何指定的前綴開(kāi)頭,則默認(rèn)按照原始的 productid
進(jìn)行排序。
-- 如果前面還有指定的順序的話 SELECT product_id, name_t_ch FROM product p ORDER BY CASE WHEN product_id = 'MP-0800004' THEN 1 WHEN product_id = 'MP-0800007' THEN 2 WHEN product_id = 'MR-0800001' THEN 3 WHEN product_id = 'MR-0800002' THEN 4 WHEN product_id = 'MR-0800004' THEN 5 WHEN product_id = 'SD-0800001' THEN 6 WHEN product_id LIKE 'WN-%' THEN 7 WHEN product_id LIKE 'HHC-%' THEN 8 WHEN product_id LIKE 'HS-%' THEN 9 WHEN product_id LIKE 'G-%' THEN 10 ELSE 11 END, product_id;
結(jié)果
從結(jié)果中可以看到已經(jīng)按照我們需要的順序排好了序。case when 的寫法更簡(jiǎn)潔,使用這個(gè)!
小結(jié)
最后這個(gè)也可以加上分頁(yè)的效果。
SELECT product_id, name_t_ch FROM product p ORDER BY CASE WHEN product_id = 'MP-0800004' THEN 1 WHEN product_id = 'MP-0800007' THEN 2 WHEN product_id = 'MR-0800001' THEN 3 WHEN product_id = 'MR-0800002' THEN 4 WHEN product_id = 'MR-0800004' THEN 5 WHEN product_id = 'SD-0800001' THEN 6 WHEN product_id LIKE 'WN-%' THEN 7 WHEN product_id LIKE 'HHC-%' THEN 8 WHEN product_id LIKE 'HS-%' THEN 9 WHEN product_id LIKE 'G-%' THEN 10 ELSE 11 END, product_id LIMIT 0, 5;
第二頁(yè)
SELECT product_id, name_t_ch FROM product p ORDER BY CASE WHEN product_id = 'MP-0800004' THEN 1 WHEN product_id = 'MP-0800007' THEN 2 WHEN product_id = 'MR-0800001' THEN 3 WHEN product_id = 'MR-0800002' THEN 4 WHEN product_id = 'MR-0800004' THEN 5 WHEN product_id = 'SD-0800001' THEN 6 WHEN product_id LIKE 'WN-%' THEN 7 WHEN product_id LIKE 'HHC-%' THEN 8 WHEN product_id LIKE 'HS-%' THEN 9 WHEN product_id LIKE 'G-%' THEN 10 ELSE 11 END, product_id LIMIT 1, 5;
以上就是MySQL實(shí)現(xiàn)字段的自定義排序的方法的詳細(xì)內(nèi)容,更多關(guān)于MySQL字段自定義排序的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
Mysql中文漢字轉(zhuǎn)拼音的實(shí)現(xiàn)(每個(gè)漢字轉(zhuǎn)換全拼)
這篇文章主要介紹了Mysql中文漢字轉(zhuǎn)拼音的實(shí)現(xiàn),并且每個(gè)漢字會(huì)轉(zhuǎn)換全拼,使用Mysql自定義函數(shù)實(shí)現(xiàn),需要的朋友可以參考下2014-06-06銀河麒麟V10安裝MySQL5.7的詳細(xì)過(guò)程
這篇文章主要介紹了銀河麒麟V10安裝MySQL5.7,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-05-05一些mysql啟動(dòng)參數(shù)的說(shuō)明和優(yōu)化方法
要求MySQL能有的連接數(shù)量。當(dāng)主要MySQL線程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請(qǐng)求,這就起作用,然后主線程花些時(shí)間(盡管很短)檢查連接并且啟動(dòng)一個(gè)新線程。2011-04-04詳談innodb的鎖(record,gap,Next-Key lock)
下面小編就為大家?guī)?lái)一篇詳談innodb的鎖(record,gap,Next-Key lock)。小編覺(jué)得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-03-03詳解在Windows環(huán)境下訪問(wèn)linux虛擬機(jī)中MySQL數(shù)據(jù)庫(kù)
這篇文章主要介紹了如何Windows環(huán)境下訪問(wèn)linux虛擬機(jī)中MySQL數(shù)據(jù)庫(kù),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04mysql 獲取當(dāng)前日期函數(shù)及時(shí)間格式化參數(shù)詳解
這篇文章主要介紹了mysql 獲取當(dāng)前日期函數(shù)now()及時(shí)間格式化DATE_FROMAT函數(shù)以及參數(shù)詳細(xì)介紹,需要的朋友可以參考下2014-08-08