MySQL:使用 greatest、least 函数获取行最大值、最小值


#MySQL ➡️ 浏览模式


max、min 是获取****的最大值、最小值。

而 greatest、least 函数可以用来分别获取****的最大值、最小值。

示例:greatest 函数

mysql> SELECT greatest(1,2)
+---------------+
| greatest(1,2) |
+---------------+
| 2             |
+---------------+

mysql> SELECT greatest(1,2,3)
+-----------------+
| greatest(1,2,3) |
+-----------------+
| 3               |
+-----------------+

mysql> SELECT greatest(1,2,3,null)
+----------------------+
| greatest(1,2,3,null) |
+----------------------+
| <null>               |
+----------------------+

mysql> SELECT greatest(1,2,3,'abc')
+-----------------------+
| greatest(1,2,3,'abc') |
+-----------------------+
| 3                     |
+-----------------------+

mysql> SELECT greatest(1,2,3,'abc',234)
+---------------------------+
| greatest(1,2,3,'abc',234) |
+---------------------------+
| 234                       |
+---------------------------+

示例:least 函数

mysql> select least(1,2,3)
+--------------+
| least(1,2,3) |
+--------------+
| 1            |
+--------------+

mysql> select least(1,2,3, null)
+--------------------+
| least(1,2,3, null) |
+--------------------+
| <null>             |
+--------------------+

示例:在表中使用 greatest、least 函数

建表:

create table `test_table` (
    `num1` int,
    `num2` int
) engine = InnoDB default charset = utf8mb4;

准备数据:

insert into test_table (num1, num2) values(1, 3);
insert into test_table (num1, num2) values(7, 5);

查询示例:

mysql> select num1, num2, greatest(num1, num2), least(num1, num2) from test_table;
+------+------+----------------------+-------------------+
| num1 | num2 | greatest(num1, num2) | least(num1, num2) |
+------+------+----------------------+-------------------+
| 1    | 3    | 3                    | 1                 |
| 7    | 5    | 7                    | 5                 |
+------+------+----------------------+-------------------+

( 本文完 )