MariaDB – Condition Group

測試環境為 CentOS 8 x86_64 虛擬機.

在 Group 資料時可以使用 IF 判斷式來決定使用哪個欄位,看一下下面範例.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-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;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
   
Database changed

建立測試用資料表.

MariaDB [testdb]> CREATE TABLE Group_Condition (ID INT , Filter1 VARCHAR(50) , Filter2 VARCHAR(50));
Query OK, 0 rows affected (0.003 sec)

MariaDB [testdb]>  INSERT Group_Condition VALUES(1 ,'' , 'A') ,(2 ,'' , 'B') , (3 , '' , 'C') , (4 , 'A' , '') , (5 , 'B' , '') , (6 , 'A' ,
 'B');
Query OK, 6 rows affected (0.001 sec)
Records: 6  Duplicates: 0  Warnings: 0

檢視一下資料.

MariaDB [testdb]> SELECT * FROM Group_Condition;
+------+---------+---------+
| ID   | Filter1 | Filter2 |
+------+---------+---------+
|    1 |         | A       |
|    2 |         | B       |
|    3 |         | C       |
|    4 | A       |         |
|    5 | B       |         |
|    6 | A       | B       |
+------+---------+---------+
6 rows in set (0.000 sec)

在 Group 資料時用 IF 判斷式來決定使用哪個欄位

MariaDB [testdb]> SELECT GROUP_CONCAT(ID) , Filter1 , Filter2 
FROM Group_Condition GROUP BY IF(Filter1 NOT LIKE '' , Filter1 , Filter2);
+------------------+---------+---------+
| GROUP_CONCAT(ID) | Filter1 | Filter2 |
+------------------+---------+---------+
| 1,4,6            |         | A       |
| 2,5              |         | B       |
| 3                |         | C       |
+------------------+---------+---------+
3 rows in set (0.000 sec)

程式說明:
最主要就是在 GROUP BY 時加上判斷式 IF(Filter1 NOT LIKE ” , Filter1 , Filter2) 當 Filter1 為空字串時就用 Filter2 當作 GROUP 的條件.