MySQL greatest()和least()函式

2019-10-16 22:55:26

在本教學中,您將學習如何使用MySQL GREATESTLEAST函式分別找到兩個或多個欄位的最大和最小值。

MySQL GREATEST和LEAST函式介紹

GREATESTLEAST函式都使用N個引數,並分別返回最大和最小值。下面說明GREATESTLEAST函式的語法:

GREATEST(value1, value2, ...);
LEAST(value1,value2,...);

引數可能具有混合資料型別。以下比較規則適用於這兩個函式:

  • 如果任何引數為NULL,則兩個函式都將立即返回NULL,而不進行任何比較。
  • 如果在INTREAL上下文中使用函式,或者所有引數都是整數值或REAL值,那麼它們將分別作為INTREAL來比較。
  • 如果引數由數位和字串組成,則函式將它們作為數位進行比較。
  • 如果至少一個引數是非二進位制(字元)字串,則函式將將引數作為非二進位制字串進行比較。
  • 在所有其他情況下,函式將引數作為二進位制字串進行比較

以下範例演示了GREATESTLEAST函式的工作原理。

SELECT GREATEST(10, 20, 30),  -- 30
       LEAST(10, 20, 30); -- 10


SELECT GREATEST(10, null, 30),  -- null
       LEAST(10, null , 30); -- null

MySQL GREATEST和最小值的例子

下面為了演示,我們來建立一個名稱為:revenues的新表。

USE testdb;

CREATE TABLE IF NOT EXISTS revenues (
    company_id INT PRIMARY KEY,
    q1 DECIMAL(19 , 2 ),
    q2 DECIMAL(19 , 2 ),
    q3 DECIMAL(19 , 2 ),
    q4 DECIMAL(19 , 2 )
);

revenues表由company_id作為主鍵,其它四個列分別儲存每季度公司的收入。

以下語句在revenues表中插入兩行 -

INSERT INTO revenues(company_id,q1,q2,q3,q4)
VALUES (1,100,120,110,130),
       (2,250,260,300,310);

要獲得每個公司的最高和最低收入,可使用GREATESTLOWEST,如下查詢語句:

SELECT 
    company_id,
    LEAST(q1, q2, q3, q4) low,
    GREATEST(q1, q2, q3, q4) high
FROM
    revenues;

執行上面查詢語句,得到以下結果 -

+------------+--------+--------+
| company_id | low    | high   |
+------------+--------+--------+
|          1 | 100.00 | 130.00 |
|          2 | 250.00 | 310.00 |
+------------+--------+--------+
2 rows in set

如果任何引數為NULL,則GREATESTLEAST函式都返回NULL,這可能不是我們想要的。為了避免這種情況,可以使用IFNULL函式將NULL視為零來執行數位比較。

以下語句向revenues表中插入在q4列中帶有NULL值的一條新行。

INSERT INTO revenues(company_id,q1,q2,q3,q4)
VALUES (3,100,120,110,null);

如果使用GREATESTLEAST函式來查詢資料,則可以得到按預期設計的結果。

SELECT 
    company_id,
    LEAST(q1, q2, q3, q4) low,
    GREATEST(q1, q2, q3, q4) high
FROM
    revenues;

執行上面查詢語句,得到以下結果 -

+------------+--------+--------+
| company_id | low    | high   |
+------------+--------+--------+
|          1 | 100.00 | 130.00 |
|          2 | 250.00 | 310.00 |
|          3 | NULL   | NULL   |
+------------+--------+--------+
3 rows in set

如上面查詢所見,其ID=3的公司的最低值和最高值為NULL

為避免這種情況,您可以使用IFNULL函式,如下:

SELECT 
    company_id,
    LEAST(IFNULL(q1, 0),
            IFNULL(q2, 0),
            IFNULL(q3, 0),
            IFNULL(q4, 0)) low,
    GREATEST(IFNULL(q1, 0),
            IFNULL(q2, 0),
            IFNULL(q3, 0),
            IFNULL(q4, 0)) high
FROM
    revenues;

執行上面查詢語句,得到以下結果 -

+------------+--------+--------+
| company_id | low    | high   |
+------------+--------+--------+
|          1 | 100.00 | 130.00 |
|          2 | 250.00 | 310.00 |
|          3 | 0.00   | 120.00 |
+------------+--------+--------+
3 rows in set

在本教學中,您已經學習了如何使用MySQL GREATESTLEAST函式來查詢值列表中最大和最小的值。