OceanBase MAX

?MAX? 函數(shù)返回參數(shù)中指定的列中的最大值。

語法

MAX([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]

作為分析函數(shù)使用時(shí)，您需要使用窗口函數(shù)的完整語法，它對一組行的集合進(jìn)行計(jì)算并返回多個(gè)值。作為聚合函數(shù)使用時(shí)，該函數(shù)對一組行的集合進(jìn)行聚合計(jì)算，結(jié)果只能返回一個(gè)值，此時(shí)不需要加 ?OVER? 關(guān)鍵字。

參數(shù)

返回類型

返回與 ?expr? 相同的數(shù)據(jù)類型值。

示例

分析函數(shù)示例

以下語句創(chuàng)建了表 employees，并向里面插入數(shù)據(jù)：

CREATE TABLE employees (manager_id INT, last_name varchar(50), hiredate varchar(50), SALARY INT);
INSERT INTO employees VALUES(100, 'Wei',  '2019-09-11',17000);     
INSERT INTO employees VALUES(100, 'Red', '2019-11-05', 17000);
INSERT INTO employees VALUES(101, 'Part',  '2018-10-01',12008); 
INSERT INTO employees VALUES(102, 'Wei',  '2019-09-11',9000);     
INSERT INTO employees VALUES(103, 'Red', '2019-11-05', 6000);
INSERT INTO employees VALUES(104, 'Part',  '2018-10-01',8000); 
COMMIT;

執(zhí)行以下語句查詢 SALARY 列的最大值：

SELECT manager_id, last_name, salary FROM (SELECT manager_id, last_name, salary, 
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees) WHERE salary = rmax_sal ORDER BY manager_id, last_name, salary;

查詢結(jié)果如下：

+------------+-----------+--------+
| MANAGER_ID | LAST_NAME | SALARY |
+------------+-----------+--------+
|        100 | Red       |  17000 |
|        100 | Wei       |  17000 |
|        101 | Part      |  12008 |
|        102 | Wei       |   9000 |
|        103 | Red       |   6000 |
|        104 | Part      |   8000 |
+------------+-----------+--------+

聚合函數(shù)示例

執(zhí)行以下語句查詢 SALARY 列的最大值：

SELECT MAX(salary) FROM employees;

查詢結(jié)果如下：

+-------------+
| MAX(SALARY) |
+-------------+
|       17000 |
+-------------+