測試環境為 CentOS 8 x86_64 (虛擬機)
- 字串函數 ( String Functions ) 與 型別轉換 – https://benjr.tw/102953
- 數字函數 (Numeric Functions) – https://benjr.tw/101970
- 日期與時間函數 (Date & Time Functions) – https://benjr.tw/102964
- 資訊函數 (Information Functions) – https://benjr.tw/102961
- 搭配 GROUP BY 函數 (Aggregate Functions) – https://benjr.tw/102967
- 流程控制函數 (Control Flow Functions) – 這邊介紹.
在 SQL SELECT 時可以用 IF THEN ELSE (Flow Control Statements) 來做判斷嗎? 不行只能用下面幾種 Control Flow Functions 來完成.
MariaDB [(none)]> HELP Control flow functions; You asked for help about help category: "Control flow functions" For more information, type 'help <item>', where <item> is one of the following topics: CASE OPERATOR IF FUNCTION IFNULL NULLIF
- CASE OPERATOR
- IF FUNCTION
- IFNULL
- NULLIF
先建立一個資料庫 (testdb) , 與 tables (employee) 格式為 Name VARCHAR(20), Dept VARCHAR(20), jobTitle VARCHAR(20) 各 20 個字元以及 數字欄位 Age INT(3) , Seniority INT(3) , Salary INT(11).
[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.001 sec) MariaDB [(none)]> USE testdb; Database changed MariaDB [testdb]> CREATE TABLE employee (Name VARCHAR(20) , Dept VARCHAR(20) , JobTitle VARCHAR(20) ,Age INT(3) , Seniority INT(3) , Salary INT(11)); Query OK, 0 rows affected (0.009 sec) MariaDB [testdb]> DESCRIBE employee; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | Name | varchar(20) | YES | | NULL | | | Dept | varchar(20) | YES | | NULL | | | JobTitle | varchar(20) | YES | | NULL | | | Age | int(3) | YES | | NULL | | | Seniority | int(3) | YES | | NULL | | | Salary | int(11) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 6 rows in set (0.005 sec)
透過 INSERT 新增加了五筆資料.
MariaDB [testdb]> INSERT INTO employee VALUES ('Ben' , 'Testing' , 'Engineer' , '32' , '5' , '45000') , ('Afa' , 'Power' , 'Engineer' , '28' , '2' , '48000') , ('Boss' , 'Testing' , 'Manager' , '42' , '8' , '75000') , ('Justin' , 'Testing' , 'Senior Engineer' , ' 26' , '2' , '42000') , ('Jasmine' , 'Power' , 'Manager' , '41' , '9' ,'80000') ;
Query OK, 5 rows affected (0.002 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM employee;
+---------+---------+-----------------+------+-----------+--------+
| Name | Dept | JobTitle | Age | Seniority | Salary |
+---------+---------+-----------------+------+-----------+--------+
| Ben | Testing | Engineer | 32 | 5 | 45000 |
| Afa | Power | Engineer | 28 | 2 | 48000 |
| Boss | Testing | Manager | 42 | 8 | 75000 |
| Justin | Testing | Senior Engineer | 26 | 2 | 42000 |
| Jasmine | Power | Manager | 41 | 9 | 80000 |
+---------+---------+-----------------+------+-----------+--------+
5 rows in set (0.001 sec)
- CASE – Case operator
雖然在 SELECT 時不可以使用 IF THEN ELSE ( Flow Control Statements ) ,但是可以使用 CASE ,使用上跟在 Flow Control Statements – https://benjr.tw/101629 的 CASE 一樣.MariaDB [astl]> HELP CASE OPERATOR; Name: 'CASE OPERATOR' Description: Syntax: CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part. URL: https://mariadb.com/kb/en/case-operator/
我們來算看看每一個人的薪水是否高於平均值,是則回傳 Above Average 否則回傳 Under Average .
MariaDB [astl]> SELECT Name , JobTitle , Salary, CASE WHEN Salary >= (SELECT SUM(Salary) DIV COUNT(*) FROM employee) THEN "Above Average" ELSE "Under Average" END AS Average FROM employee; +---------+-----------------+--------+---------------+ | Name | JobTitle | Salary | Average | +---------+-----------------+--------+---------------+ | Ben | Engineer | 45000 | Under Average | | Afa | Engineer | 48000 | Under Average | | Boss | Manager | 75000 | Above Average | | Justin | Senior Engineer | 42000 | Under Average | | Jasmine | Manager | 80000 | Above Average | +---------+-----------------+--------+---------------+ 5 rows in set (0.001 sec)
主要是這一段 (SELECT SUM(Salary) DIV COUNT(*) FROM employee) ,先算出總薪水的平均值再來判斷,看是否高於或是低於平均值.
CASE WHEN Salary >= (SELECT SUM(Salary) DIV COUNT(*) FROM employee) THEN "Above Average" ELSE "Under Average" END
函數說明：
- SUM
計算數字加總. - COUNT
回傳 SELECT 的筆數.
- SUM
- IF() – If/else construct
雖然在 SELECT 時不可以使用 IF THEN ELSE ( Flow Control Statements ),但是可以使用 IF 函數.MariaDB [astl]> HELP IF FUNCTION; Name: 'IF FUNCTION' Description: Syntax: IF(expr1,expr2,expr3) If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used. URL: https://mariadb.com/kb/en/if-function/
IF(expr1,expr2,expr3) , 當 expr1 成立時回傳 expr2 否則回傳 expr3
只有 NULL 才是 NULL.
MariaDB [testdb]> SELECT IF( NULL IS NULL,"NULL","Not NULL"); +-------------------------------------+ | IF( NULL IS NULL,"NULL","Not NULL") | +-------------------------------------+ | NULL | +-------------------------------------+ 1 row in set (0.002 sec)
“NULL” (代表字串 ),0(數字) 也不是 NULL.
MariaDB [testdb]> SELECT IF( "NULL" IS NULL,"NULL","Not NULL"); +---------------------------------------+ | IF( "NULL" IS NULL,"NULL","Not NULL") | +---------------------------------------+ | Not NULL | +---------------------------------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT IF( 0 IS NULL,"NULL","Not NULL"); +----------------------------------+ | IF( 0 IS NULL,"NULL","Not NULL") | +----------------------------------+ | Not NULL | +----------------------------------+ 1 row in set (0.001 sec) MariaDB [testdb]> SELECT IF( 1 IS NULL,"NULL","Not NULL"); +----------------------------------+ | IF( 1 IS NULL,"NULL","Not NULL") | +----------------------------------+ | Not NULL | +----------------------------------+ 1 row in set (0.000 sec)
MariaDB [astl]> SELECT IF(1>2,2,3); +-------------+ | IF(1>2,2,3) | +-------------+ | 3 | +-------------+ 1 row in set (0.000 sec)
一樣我們來算看看每一個人的薪水是否高於平均值 (但使用 IF 函數),是則回傳 Above Average 否則回傳 Under Average .
MariaDB [astl]> SELECT Name , JobTitle , Salary, IF (Salary >= (SELECT SUM(Salary) DIV COUNT(*) FROM employee) , "Above Average" , "Under Average") AS Average FROM employee; +---------+-----------------+--------+---------------+ | Name | JobTitle | Salary | Average | +---------+-----------------+--------+---------------+ | Ben | Engineer | 45000 | Under Average | | Afa | Engineer | 48000 | Under Average | | Boss | Manager | 75000 | Above Average | | Justin | Senior Engineer | 42000 | Under Average | | Jasmine | Manager | 80000 | Above Average | +---------+-----------------+--------+---------------+ 5 rows in set (0.001 sec)
- IFNULL() – Null if/else construct
MariaDB [astl]> HELP IFNULL; Name: 'IFNULL' Description: Syntax: IFNULL(expr1,expr2) If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used. URL: https://mariadb.com/kb/en/ifnull/
用來判斷 Expression 字串是否為 NULL ,是就以 ALTER_Value 來取代.
IFNULL(Expression, ALTER_Value)
MariaDB [testdb]> SELECT IFNULL(NULL,'Ben') AS 'IFNULL Result'; +---------------+ | IFNULL Result | +---------------+ | Ben | +---------------+ 1 row in set (0.002 sec) MariaDB [testdb]> SELECT IFNULL(1,'Ben') 'IFNULL Result'; +---------------+ | IFNULL Result | +---------------+ | 1 | +---------------+ 1 row in set (0.000 sec)
下面範例使用 GROUP BY (依據不同單位做分類,再加總各單位的薪水 )加上 WITH ROLLUP (可以把分類的資料做個總結).
MariaDB [astl]> SELECT Dept, SUM(salary) AS Total FROM employee GROUP BY Dept WITH ROLLUP; +---------+--------+ | Dept | Total | +---------+--------+ | Power | 128000 | | Testing | 162000 | | NULL | 290000 | +---------+--------+ 3 rows in set (0.001 sec)
但很討厭的是在總結資料中會顯示為 NULL ,這時候可以利用 IFNULL 來取代 NULL 字串.
MariaDB [astl]> SELECT IFNULL(Dept,'SUM') AS Dept, SUM(salary) AS Total FROM employee GROUP BY Dept WITH ROLLUP; +---------+--------+ | Dept | Total | +---------+--------+ | Power | 128000 | | Testing | 162000 | | SUM | 290000 | +---------+--------+ 3 rows in set, 1 warning (0.002 sec)
- NULLIF() – Return NULL if expr1 = expr2
MariaDB [astl]> HELP NULLIF; Name: 'NULLIF' Description: Syntax: NULLIF(expr1,expr2) Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END. URL: https://mariadb.com/kb/en/nullif/
當兩個值相同時回傳 NULL ,不同時則回傳第一個值.
MariaDB [astl]> SELECT NULLIF(1,1); +-------------+ | NULLIF(1,1) | +-------------+ | NULL | +-------------+ 1 row in set (0.001 sec) MariaDB [astl]> SELECT NULLIF(1,2); +-------------+ | NULLIF(1,2) | +-------------+ | 1 | +-------------+ 1 row in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容