MYSQL滿足條件函式里放查詢最大函式的方法

TechSynapse發表於2024-05-26
MySql函式

1.MYSQL滿足條件函式里放查詢最大函式的方法

在MySQL中，如果我們想要在一個條件函式（如CASE）內部使用聚合函式（如MAX）來獲取某個欄位的最大值，我們通常需要在外部查詢或子查詢中執行這個聚合操作，並將結果作為引數傳遞給條件函式。

以下是一個具體的程式碼示例，假設我們有一個名為sales的表，它有兩個欄位：product_id和sale_price。我們想要找出每個product_id的最大sale_price，並在一個CASE語句中根據這個最大值來決定如何顯示一個額外的欄位price_status。

SELECT   
    product_id,  
    sale_price,  
    (  
        SELECT   
            CASE   
                WHEN sale_price = MAX(t2.sale_price) THEN 'Max Price'  
                ELSE 'Not Max Price'  
            END  
        FROM sales t2  
        WHERE t2.product_id = t1.product_id  
    ) AS price_status  
FROM sales t1  
GROUP BY product_id, sale_price;

但是，請注意，上面的查詢可能不會按我們期望的方式工作，因為它會為每個product_id和sale_price的組合返回一個price_status。如果我們只想為每個product_id返回一行，並且只顯示最大sale_price的price_status為'Max Price'，其他為'Not Max Price'（但這在這種情況下沒有實際意義，因為我們只關心最大值），我們可以使用以下查詢：

SELECT   
    t1.product_id,  
    t1.sale_price,  
    CASE   
        WHEN t1.sale_price = (SELECT MAX(sale_price) FROM sales t2 WHERE t2.product_id = t1.product_id) THEN 'Max Price'  
        ELSE 'Not Max Price'  -- 這裡實際上對於非最大值的行是多餘的，因為我們不會顯示它們  
    END AS price_status  
FROM sales t1  
INNER JOIN (  
    SELECT product_id, MAX(sale_price) AS max_sale_price  
    FROM sales  
    GROUP BY product_id  
) t3 ON t1.product_id = t3.product_id AND t1.sale_price = t3.max_sale_price;

這個查詢首先在一個子查詢中為每個product_id找到最大的sale_price，然後在外部查詢中透過INNER JOIN來只選擇那些具有最大sale_price的行，併為它們設定price_status為'Max Price'。其他行（如果有的話）將不會被選擇，因此不需要為它們設定price_status。

2.MySQL中使用CASE語句和MAX函式的程式碼示例

為了更好的理解，我們給出一些更具體的例子，展示瞭如何在MySQL中使用CASE語句和MAX函式。

2.1顯示每個產品的最高售價和狀態

假設我們有一個名為products_sales的表，其中包含product_id（產品ID）和sale_price（售價）兩個欄位。我們想要顯示每個產品的最高售價，併為其新增一個狀態欄位price_status，表示是否為最高售價。

SELECT   
    p.product_id,  
    p.max_sale_price AS highest_sale_price,  
    CASE   
        WHEN p.max_sale_price IS NOT NULL THEN 'Max Price'  
        ELSE 'No Sales'  -- 如果某個產品沒有銷售記錄，則顯示'No Sales'  
    END AS price_status  
FROM (  
    SELECT   
        product_id,  
        MAX(sale_price) AS max_sale_price  
    FROM products_sales  
    GROUP BY product_id  
) p;

2.2顯示所有銷售記錄，並標記最高售價

如果我們想要顯示所有銷售記錄，並標記哪些記錄是對應產品的最高售價，我們可以使用子查詢和JOIN操作。

SELECT   
    s.product_id,  
    s.sale_price,  
    CASE   
        WHEN s.sale_price = (SELECT MAX(sale_price) FROM products_sales ps WHERE ps.product_id = s.product_id) THEN 'Max Price'  
        ELSE 'Not Max Price'  
    END AS price_status  
FROM products_sales s;

2.3結合其他條件篩選銷售記錄

如果我們還想根據其他條件（如日期範圍）篩選銷售記錄，並標記最高售價，我們可以這樣做：

SELECT   
    s.product_id,  
    s.sale_price,  
    s.sale_date,  
    CASE   
        WHEN s.sale_price = (SELECT MAX(sale_price) FROM products_sales ps WHERE ps.product_id = s.product_id AND ps.sale_date BETWEEN '2024-01-01' AND '2024-5-26') THEN 'Max Price in 2024'  
        ELSE 'Not Max Price in 2024'  
    END AS price_status  
FROM products_sales s  
WHERE s.sale_date BETWEEN '2024-01-01' AND '2024-5-26';

在上面的例子中，我們僅考慮了2024年半年內截止今天（5月26日）的銷售記錄，並標記了哪些記錄是對應產品在2024年半年內的最高售價。

相關文章