MariaDB 函數 – MAX , MIN 與 GREATEST , LEAST 函數

測試環境為 CentOS 8 x86_64

計算欄(column) 與 列(row) 的最大與最小值可以使用哪些函數來完成呢!

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

[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 [testdb]> CREATE TABLE v1 (ID1 INT(11) NOT NULL AUTO_INCREMENT , FAN1 INT(11) NOT NULL , FAN2 INT(11) NOT NULL , FAN3 INT(11) NOT NULL ,  PRIMARY KEY (ID1));
Query OK, 0 rows affected (0.007 sec)

MariaDB [testdb]> INSERT INTO v1 (FAN1 , FAN2 , FAN3) VALUES(-14,45,33) , (22,6,88) , (2,2,2) , (34,67,899) , (1,4,77) ;
Query OK, 5 rows affected (0.001 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM v1;
+-----+------+------+------+
| ID1 | FAN1 | FAN2 | FAN3 |
+-----+------+------+------+
|   1 |  -14 |   45 |   33 |
|   2 |   22 |    6 |   88 |
|   3 |    2 |    2 |    2 |
|   4 |   34 |   67 |  899 |
|   5 |    1 |    4 |   77 |
+-----+------+------+------+
5 rows in set (0.001 sec)

欄(column) 最大與最小值

可以使用 MAX(column name) 與 MIN(column name) 函數來檢視.

MariaDB [testdb]> SELECT MAX(FAN1) , MIN(FAN1) , MAX(FAN2) , MIN(FAN2) , MAX(FAN3) , MIN(FAN3) FROM v1;
+-----------+-----------+-----------+-----------+-----------+-----------+
| MAX(FAN1) | MIN(FAN1) | MAX(FAN2) | MIN(FAN2) | MAX(FAN3) | MIN(FAN3) |
+-----------+-----------+-----------+-----------+-----------+-----------+
|        34 |       -14 |        67 |         2 |       899 |         2 |
+-----------+-----------+-----------+-----------+-----------+-----------+
1 row in set (0.001 sec)

列(row) 最大與最小值

可以使用 GREATEST(column name1 , name2 , name3) 與 LEAST(column name1 , name2 , name3) 函數來檢視.

MariaDB [testdb]> SELECT GREATEST(1,2,3,4);
+-------------------+
| GREATEST(1,2,3,4) |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.000 sec)

MariaDB [testdb]> SELECT LEAST(1,2,3,4);
+----------------+
| LEAST(1,2,3,4) |
+----------------+
|              1 |
+----------------+
1 row in set (0.000 sec)

MariaDB [testdb]> SELECT FAN1 , FAN2 , FAN3 , GREATEST(FAN1 , FAN2 , FAN3) , LEAST(FAN1 , FAN2 , FAN3) FROM v1;
+------+------+------+------------------------------+---------------------------+
| FAN1 | FAN2 | FAN3 | GREATEST(FAN1 , FAN2 , FAN3) | LEAST(FAN1 , FAN2 , FAN3) |
+------+------+------+------------------------------+---------------------------+
|  -14 |   45 |   33 |                           45 |                       -14 |
|   22 |    6 |   88 |                           88 |                         6 |
|    2 |    2 |    2 |                            2 |                         2 |
|   34 |   67 |  899 |                          899 |                        34 |
|    1 |    4 |   77 |                           77 |                         1 |
+------+------+------+------------------------------+---------------------------+
5 rows in set (0.001 sec)