MariaDB – ORDER BY CASE

測試環境為 CentOS 8 x86_64

SELECT 資料可以依據藍未來排序 ORDER BY Column,但是我想要依據某欄位內的特定字串來排序,這樣有辦法嗎?可以透過 ORDER BY CASE 來做.

先建立測試要用的資料庫.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Database changed

建立測試用資料表.

MariaDB [astl1]> CREATE TABLE testscore (ID1 INT(11) NOT NULL AUTO_INCREMENT , Score VARCHAR(20) NOT NULL , PRIMARY KEY (ID1));
Query OK, 0 rows affected (0.003 sec)

新增使用者與其測試資料.

MariaDB [astl1]> INSERT INTO testscore (Score) VALUES('High') , ('Low') , ('Medium') , ('High') , ('High') , ('Low') , ('High') , ('High');
Query OK, 8 rows affected (0.001 sec)
Records: 8  Duplicates: 0  Warnings: 0

檢視一下資料.

MariaDB [astl1]> SELECT * FROM testscore ;
+-----+--------+
| ID1 | Score  |
+-----+--------+
|   1 | High   |
|   2 | Low    |
|   3 | Medium |
|   4 | High   |
|   5 | High   |
|   6 | Low    |
|   7 | High   |
|   8 | High   |
+-----+--------+
8 rows in set (0.001 sec)

預設資料是依據文字的順序(A , B , C , D …)來顯示.

MariaDB [astl1]> SELECT Score , COUNT(*) FROM testscore GROUP BY Score;
+--------+----------+
| Score  | COUNT(*) |
+--------+----------+
| High   |        5 |
| Low    |        2 |
| Medium |        1 |
+--------+----------+
3 rows in set (0.000 sec)

但我想要讓資料依據 High , Medium , Low 來顯示時要怎麼寫 ORDER BY, 參考文章 – https://stackoverflow.com/questions/6332043/sql-order-by-multiple-values-in-specific-order .

SELECT Score , COUNT(*) FROM testscore GROUP BY Score
ORDER BY
   CASE Score
      WHEN 'High' THEN 1
      WHEN 'Medium' THEN 2
      WHEN 'Low' THEN 3
      ELSE 4 
   END;

這樣可以如需求來顯示資料的順序了.

MariaDB [astl1]> SELECT Score , COUNT(*) FROM testscore GROUP BY Score
    -> ORDER BY
    ->    CASE Score
    ->       WHEN 'High' THEN 1
    ->       WHEN 'Medium' THEN 2
    ->       WHEN 'Low' THEN 3
    ->       ELSE 4
    ->    END;
+--------+----------+
| Score  | COUNT(*) |
+--------+----------+
| High   |        5 |
| Medium |        1 |
| Low    |        2 |
+--------+----------+
3 rows in set (0.001 sec)

這個應用也可以使用在 GROUP_CONCAT 裡面的 ORDER BY