mysql满足条件函数里放查询最大函数

精选原创

灯泡将军 2024-05-18 12:20:07 ©著作权

©著作权归作者所有：来自51CTO博客作者灯泡将军的原创作品，请联系作者获取转载授权，否则将追究法律责任

在MySQL中，结合查询最大值函数（MAX()）与条件判断通常涉及到子查询或者嵌套查询。这种用法在处理复杂查询逻辑时尤为有用，比如当你需要基于某个条件找出特定分组内的最大值，然后进一步筛选结果。以下是一个综合场景的应用示例，包括如何在查询中嵌入MAX()函数并结合条件表达式，来完成更高级的数据筛选和分析。

场景设定

假设我们有一个销售数据表sales_data，包含以下列：

order_id（订单ID）
product_id（商品ID）
amount（销售金额）
sale_date（销售日期）

我们的目标是找出每个月份销售金额最大的商品ID及其最大销售额。

步骤详解

1. 分组求每个月最大销售额

首先，我们需要按月份分组，并计算每个月的最大销售额。这可以通过GROUP BY和MAX()函数结合来实现：

SELECT 
    YEAR(sale_date) AS sale_year, 
    MONTH(sale_date) AS sale_month, 
    MAX(amount) AS max_amount
FROM 
    sales_data
GROUP BY 
    sale_year, sale_month;

2. 查找每个月最大销售额对应的商品ID

上面的查询只给出了每个月的最大销售额，但没有商品ID。为了获取每个最大销售额对应的商品ID，我们需要使用子查询或者窗口函数。这里我们采用子查询的方式，先找出每个月的最大销售额，然后在这个基础上再找出对应的商品ID。

SELECT 
    sd.sale_year, 
    sd.sale_month, 
    sd.product_id, 
    sd.amount
FROM 
    sales_data sd
JOIN (
    SELECT 
        YEAR(sale_date) AS sale_year, 
        MONTH(sale_date) AS sale_month, 
        MAX(amount) AS max_amount
    FROM 
        sales_data
    GROUP BY 
        sale_year, sale_month
) AS monthly_max ON 
    sd.sale_year = monthly_max.sale_year AND 
    sd.sale_month = monthly_max.sale_month AND 
    sd.amount = monthly_max.max_amount;

这个查询首先创建了一个子查询monthly_max来获取每个月的最大销售额，然后外层查询通过连接子查询的结果，找到了每个月份中销售金额等于该月最大销售额的所有记录。

高级应用：使用窗口函数（如果MySQL版本支持）

如果MySQL版本支持窗口函数（如MySQL 8.0及以上版本），则可以直接使用RANK()或ROW_NUMBER()函数来简化查询：

SELECT 
    sale_year, 
    sale_month, 
    product_id, 
    amount
FROM (
    SELECT 
        YEAR(sale_date) AS sale_year, 
        MONTH(sale_date) AS sale_month, 
        product_id, 
        amount,
        RANK() OVER(PARTITION BY YEAR(sale_date), MONTH(sale_date) ORDER BY amount DESC) as rank
    FROM 
        sales_data
) ranked_sales
WHERE 
    rank = 1;

这个查询使用了窗口函数RANK()，它为每个月的销售记录按金额降序排名，然后外层查询仅选择排名为1的记录，即每个分组内金额最大的记录。