MariaDB / MySQL – Case

測試環境為 CentOS 7 x86_64 (虛擬機)

CASE 的語法如下,詳細請參考 https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#operator_case 官網的說明.

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 

實際來看一個範例,該物品的使用上限為 100 次,且使用期限不能超過一年.先來建立用來測試的資料.欄位為 loops(使用次數) , testloops(使用上限) 與 t1( 進貨時間 timestamp).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
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 [testdb]> create table testing (loops varchar(6), testloops varchar(6), t1 timestamp);
Query OK, 0 rows affected (0.02 sec)

並建立五筆資料.

MariaDB [testdb]> INSERT INTO testing VALUES ('9' , '100' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO testing VALUES ('100' , '100' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 DAY) );
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO testing VALUES ('78' , '100' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) );
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO testing VALUES ('100' , '100' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) );
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> INSERT INTO testing VALUES ('90' , '100' , DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR) );
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> SELECT * FROM testing;
+-------+-----------+---------------------+
| loops | testloops | t1                  |
+-------+-----------+---------------------+
| 9     | 100       | 2019-06-05 09:47:55 |
| 100   | 100       | 2019-06-03 09:48:02 |
| 78    | 100       | 2017-06-05 09:48:08 |
| 100   | 100       | 2017-06-05 09:48:13 |
| 90    | 100       | 2017-06-05 09:48:19 |
+-------+-----------+---------------------+
5 rows in set (0.01 sec)

函數說明:

  • CURRENT_TIMESTAMP()
    功能與 NOW 一樣,都是回傳目前系統的日期時間.
  • DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 2 YEAR)
    將剪去指定的時間.INTERVAL 後面可以接的時間為 DAY, YEAR, SECOND, MINUTE_SECOND, DAY_SECOND, DAY_HOUR, SECOND_MICROSECOND.

裡面用到一些日期與時間的函數,詳細使用方式請參考 https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

我們要怎麼透過這些資料 + case 的功能把物品區分為三類.

  1. Working – 使用次數在 100 次內,且沒有超過一年的使用期限.
  2. Damage – 使用次數超過 100 次了.
  3. Expire – 使用次數在 100 次內,但超過一年的使用期限.

語法如下:

MariaDB [testdb]> SELECT 
    ->     loops, 
    ->     testloops, 
    ->     t1,
    ->     CASE WHEN CAST(loops AS SIGNED) >= CAST(testloops AS SIGNED) THEN 'Damage' WHEN t1 < DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR) THEN 'Expire' ELSE 'Working' END AS Status
    -> FROM testing;
+-------+-----------+---------------------+---------+
| loops | testloops | t1                  | Status  |
+-------+-----------+---------------------+---------+
| 9     | 100       | 2019-06-05 09:47:55 | Working |
| 100   | 100       | 2019-06-03 09:48:02 | Damage  |
| 78    | 100       | 2017-06-05 09:48:08 | Expire  |
| 100   | 100       | 2017-06-05 09:48:13 | Damage  |
| 90    | 100       | 2017-06-05 09:48:19 | Expire  |
+-------+-----------+---------------------+---------+
5 rows in set (0.00 sec)

函數說明:

  • CAST(loops AS SIGNED)
    因為當初 loops , testloops 的格式都是 varchar() 文字,在比較的時候會出現問題,透過 CAST 函數作轉換(文字轉數字 SIGNED),更多關於 CAST 的使用請參考 https://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast
  • DATE_SUB(CURRENT_TIMESTAMP() , INTERVAL 1 YEAR)
    將目前時間剪一年,用來判斷是否超過一年.
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料