一、mysql 一個字段多種排序

1. 數據

   id	name
   1	tkj1000020-1.11test
   2	tkj1000020-1
   3	tkj1000020-2.1 test
   4	tkj1000020-2.2 test
   5	tkj1000020-2
   6	tkj1000020.1test
   7	tkj1000020.1test_001
   8	tkj1000020.2test
   9	tkj1000020.3test
   10	tkj1000020aest
   11	tkj1000020test
   12	tkj10000203333
   13	tkj1000020.Qtest
   14	tkj1000020-abb12
   15	tkj1000020.1 test
   16	tkj1000020.1test_002

2. 排序規則
   • 字母優先 按 26 個字母順序排列
   • 其次字符 .，.[a-zA-Z] 需要排在 .[0-9] 前面
   • 其次字符 -，-[a-zA-Z] 需要排在 -[0-9] 前面
   • 其次字符 - 緊跟數字然后是字母，優先數字排序然后是字母排序
3. sql

   SELECT * FROM 
   (
      SELECT Name,
      (
      CASE
         -- 1. 字母結尾并且數據不包含字符 '.'、 '-'，排第一
         WHEN Name REGEXP '.*[a-zA-Z]$' AND Name NOT REGEXP '[\\.\\-]' THEN 0
         -- 2. .[a-zA-Z] 整個數據不包含字符 '-'，排第二
         WHEN Name REGEXP '\\.[a-zA-Z]' AND Name NOT REGEXP '[\\-]'  THEN 1
         -- 3. .[0-9] 整個數據不包含字符 '-'，排第三
         WHEN Name REGEXP '\\.[0-9]'  AND Name NOT REGEXP '[\\-]'  THEN 2
         -- 4. -[a-zA-Z]， 排第四
         WHEN Name REGEXP '\\-[a-zA-Z]' THEN 3
         -- 5. -[0-9]， 排第五
         WHEN Name REGEXP '\\-[0-9]' THEN 4
         -- 6. [\\.\\-] 整個數據不包含 '.' 、 '-'，排第六
         WHEN Name NOT REGEXP '[\\.\\-]' THEN  5
         -- 7. 其他默認排最后
         ELSE 6
      END
      ) AS Sort
      FROM `Database.Table` where Type ='tkj1000020'
   ) As Data ORDER BY Sort,Name; -- 先根據 Sort 排序，然后根據 Name 排序
   

4. 排序結果

   id	name	sort
   1	tkj1000020aest	0
   2	tkj1000020test	0
   3	tkj1000020.Qtest	1
   4	tkj1000020.1 test	2
   5	tkj1000020.1test	2
   6	tkj1000020.1test_001	2
   7	tkj1000020.1test_002	2
   8	tkj1000020.2test	2
   9	tkj1000020.3test	2
   10	tkj1000020-abb12	3
   11	tkj1000020-1	4
   12	tkj1000020-1.11test	4
   13	tkj1000020-2	4
   14	tkj1000020-2.1 test	4
   15	tkj1000020-2.2 test	4
   16	tkj10000203333	5

5. 待續

二、文件名排序

1. 數據

   id	name
   1	fkj1000022-1.png
   2	fkj1000022-1.11test.jpg
   3	fkj1000022-2.1test.png
   4	fkj1000022-2.2test.png
   5	fkj1000022-2_002.jpg
   6	fkj1000022.1test.png
   7	fkj1000022.1test_001.jpg
   8	fkj1000022.2test.png
   9	fkj1000022.3test.jpg
   10	fkj10000223333.png
   11	fkj1000022test.jpg
   12	fkj1000022aest.jpg

2. 排序規則
   • 字母優先 按 26 個字母順序排列
   • 其次字符 .，.[a-zA-Z] 需要排在 .[0-9] 前面
   • 其次字符 -，-[a-zA-Z] 需要排在 -[0-9] 前面
   • 其次字符 - 緊跟數字然后是字母，優先數字排序然后是字母排序
3. sql

   -- 正則文件名（無后綴）
   SELECT REGEXP_REPLACE('fkj1000022-2.1test.jpg', '\\.[^.\\/]+$', '')
   -- 輸出結果（fkj1000022-2.1test）
   
   SELECT * FROM 
   (
    SELECT Id,REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') as Rename,
    (
     CASE
        -- 1、正則文件名（無后綴），字母結尾并且數據不包含字符 '.'、 '-'，排第一
        WHEN REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') REGEXP '.*[a-zA-Z]$' AND REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') NOT REGEXP '[\\.\\-]' THEN 0
        -- 2. 正則文件名（無后綴），.[a-zA-Z] 整個數據不包含字符 '-'，排第二
        WHEN REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') REGEXP '\\.[a-zA-Z]' AND REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') NOT REGEXP '[\\-]'  THEN 1
        -- 3. 正則文件名（無后綴），.[0-9] 整個數據不包含字符 '-'，排第三
        WHEN REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') REGEXP '\\.[0-9]'  AND REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') NOT REGEXP '[\\-]'  THEN 2
        -- 4. 正則文件名（無后綴），-[a-zA-Z]， 排第四
        WHEN REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') REGEXP '\\-[a-zA-Z]' THEN 3
        -- 5. 正則文件名（無后綴）， -[0-9]， 排第五
        WHEN REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '') REGEXP '\\-[0-9]' THEN 4
        -- 6. 正則文件名（無后綴），[\\.\\-] 整個數據不包含 '.' 、 '-'，排第六
        WHEN REGEXP_REPLACE(Name, '\\.[^.\\/]+$', '')NOT REGEXP '[\\.\\-]' THEN  6
        -- 7. 其他默認排最后
        ELSE 7
     END
    ) AS Sort
    FROM DataBase.`Table` where Id = 7338686932664384
   ) As Data ORDER BY Sort,Rename ASC -- 先根據 Sort 排序，然后根據 Rename 排序;
   

4. 排序結果

   id	rename	sort
   1	fkj1000022aest	0
   2	fkj1000022test	0
   3	fkj1000022.1test	2
   4	fkj1000022.1test_001	2
   5	fkj1000022.2test	2
   6	fkj1000022.3test	2
   7	fkj1000022-1	4
   8	fkj1000022-1.11test	4
   9	fkj1000022-2_002	4
   10	fkj1000022-2.1test	4
   11	fkj1000022-2.2test	4
   12	fkj10000223333	6

5. 待續