mysql 一个字段多种排序方式

一、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. 待续