如何在MySQL中使用GROUP BY、LIMIT和SUM实现特定条件查询

原始数据 #

需求说明 #

给定P=2，Q=3，找出满足以下所有条件的A_id：

• 该A_id对应的前P条最大L记录之和 ≥ P+Q（即5）
• 该A_id对应的前P条最大M记录之和 ≥ P（即2）
• 该A_id对应的前P条最大N记录之和 ≥ Q（即3）

MySQL实现方案 #

方案1：使用窗口函数（MySQL 8.0及以上版本） #

窗口函数ROW_NUMBER()可以方便地为每个A_id分组内的记录按指定字段排序并编号，再筛选前P条求和：

WITH sum_L AS (
    SELECT 
        A_id,
        SUM(L) AS total_L
    FROM (
        SELECT 
            A_id,
            L,
            ROW_NUMBER() OVER (PARTITION BY A_id ORDER BY L DESC) AS rn
        FROM your_table_name -- 替换为你的表名
    ) t
    WHERE rn <= 2 -- 对应P=2
    GROUP BY A_id
),
sum_M AS (
    SELECT 
        A_id,
        SUM(M) AS total_M
    FROM (
        SELECT 
            A_id,
            M,
            ROW_NUMBER() OVER (PARTITION BY A_id ORDER BY M DESC) AS rn
        FROM your_table_name
    ) t
    WHERE rn <= 2
    GROUP BY A_id
),
sum_N AS (
    SELECT 
        A_id,
        SUM(N) AS total_N
    FROM (
        SELECT 
            A_id,
            N,
            ROW_NUMBER() OVER (PARTITION BY A_id ORDER BY N DESC) AS rn
        FROM your_table_name
    ) t
    WHERE rn <= 2
    GROUP BY A_id
)
SELECT sl.A_id
FROM sum_L sl
JOIN sum_M sm ON sl.A_id = sm.A_id
JOIN sum_N sn ON sl.A_id = sn.A_id
WHERE sl.total_L >= 5 -- P+Q=5
  AND sm.total_M >= 2 -- P=2
  AND sn.total_N >= 3; -- Q=3

代码解释

1. CTE定义：三个CTE分别计算每个A_id的前2条最大L、M、N的总和：
   • PARTITION BY A_id：按A_id分组
   • ORDER BY 字段 DESC：按目标字段降序排序，确保取最大的前P条
   • ROW_NUMBER()：为每组内的记录生成从1开始的排名
   • 筛选rn <= 2的记录后，按A_id分组求和得到对应字段的总和
2. 关联与筛选：将三个CTE通过A_id关联，筛选同时满足三个求和条件的A_id

方案2：使用用户变量（兼容MySQL 5.x版本） #

对于不支持窗口函数的旧版本MySQL，可用用户变量模拟排名功能：

SELECT sl.A_id
FROM (
    SELECT 
        A_id,
        SUM(L) AS total_L
    FROM (
        SELECT 
            A_id,
            L,
            @rn_l := IF(@prev_a = A_id, @rn_l + 1, 1) AS rn,
            @prev_a := A_id
        FROM your_table_name, (SELECT @prev_a := NULL, @rn_l := 0) vars
        ORDER BY A_id, L DESC
    ) t
    WHERE rn <= 2
    GROUP BY A_id
) sl
JOIN (
    SELECT 
        A_id,
        SUM(M) AS total_M
    FROM (
        SELECT 
            A_id,
            M,
            @rn_m := IF(@prev_a = A_id, @rn_m + 1, 1) AS rn,
            @prev_a := A_id
        FROM your_table_name, (SELECT @prev_a := NULL, @rn_m := 0) vars
        ORDER BY A_id, M DESC
    ) t
    WHERE rn <= 2
    GROUP BY A_id
) sm ON sl.A_id = sm.A_id
JOIN (
    SELECT 
        A_id,
        SUM(N) AS total_N
    FROM (
        SELECT 
            A_id,
            N,
            @rn_n := IF(@prev_a = A_id, @rn_n + 1, 1) AS rn,
            @prev_a := A_id
        FROM your_table_name, (SELECT @prev_a := NULL, @rn_n := 0) vars
        ORDER BY A_id, N DESC
    ) t
    WHERE rn <= 2
    GROUP BY A_id
) sn ON sl.A_id = sn.A_id
WHERE sl.total_L >= 5
  AND sm.total_M >= 2
  AND sn.total_N >= 3;

代码解释

• @prev_a：记录上一条的A_id，用于判断是否在同一分组
• @rn_l/@rn_m/@rn_n：记录当前分组内的排名，当A_id变化时重置为1，否则递增
• 按A_id和目标字段降序排序后，生成排名并筛选前2条求和，后续关联筛选逻辑同方案1

结果验证 #

根据原始数据计算：

• A_id=1：前2条L和为4+2=6≥5；前2条M和为3+2=5≥2；前2条N和为2+1=3≥3 → 满足条件
• 其他A_id均不满足所有条件，最终结果为A_id=1

内容的提问来源于stack exchange，提问作者Saka45