MySQL實(shí)現(xiàn)字段的自定義排序的方法

更新時(shí)間：2024年04月12日 08:59:10 作者：天空sky藍(lán)

一般情況下,我們排序都是直接利用 order by 字段 asc/desc;但是如果要排序的字段數(shù)據(jù)格式并不能直接實(shí)現(xiàn),或者說(shuō)我們需要指定的順序且沒(méi)有什么規(guī)律,簡(jiǎn)單的order by字段就實(shí)現(xiàn)不了,所以本文給大家介紹了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è)，如果使用代碼排序的話(huà)，則需要查出所有數(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解釋?zhuān)涸谶@里在 FIELD 函數(shù)中，第一個(gè)參數(shù)指定了要排序的字段或表達(dá)式，而后續(xù)的參數(shù)指定了排序的順序。在您提供的查詢(xún)中，CASE WHEN 表達(dá)式返回的結(jié)果是字符串，這些字符串用于指定排序順序。因此，product_id 不能直接寫(xiě)在 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è)查詢(xún)首先根據(jù) productid 是否以特定前綴開(kāi)頭進(jìn)行分類(lèi)，并為每個(gè)分類(lèi)分配一個(gè)排序權(quán)重。然后，按照這個(gè)排序權(quán)重進(jìn)行排序。如果 productid 不以任何指定的前綴開(kāi)頭，則默認(rèn)按照原始的 productid 進(jìn)行排序。

-- 如果前面還有指定的順序的話(huà)
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 的寫(xiě)法更簡(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;