MySQL按重量匹配样本 总重≤200磅的轻重样本配对查询

业务规则落地逻辑 #

配对流程严格遵循需求规则，按以下步骤循环执行直到所有样本处理完毕：

• 从当前待配对的样本池中，取出重量最大的样本
• 尝试将该最重样本与当前待配对池中重量最小的样本组合
• 若两者总重量≤200磅上限，则配对成功，两个样本同时移出待配对池
• 若两者总重量超过200磅，则最重样本无法和任何剩余样本配对（剩余其他样本都比当前最轻样本重，加起来必然超限），该样本单独成组移出待配对池

可直接运行的SQL代码 #

假设你的sample表核心字段为：样本IDsample_id、产品IDproduct_id、样本重量pound，针对指定产品执行配对的代码如下：

WITH sorted_samples AS (
    -- 单次排序完成样本权重打标，避免重复计算
    SELECT
        sample_id,
        pound,
        ROW_NUMBER() OVER (ORDER BY pound ASC) AS light_idx,
        ROW_NUMBER() OVER (ORDER BY pound DESC) AS heavy_idx,
        COUNT(*) OVER () AS total_count
    FROM sample
    WHERE product_id = '替换为你需要处理的目标产品ID'
),
pair_recursion AS (
    -- 递归初始化：左指针指向最轻样本位，右指针指向最重样本位
    SELECT
        total_count,
        1 AS left_ptr,
        total_count AS right_ptr,
        CAST(NULL AS CHAR(20)) AS group_no,
        sample_id,
        pound
    FROM sorted_samples
    WHERE heavy_idx = 1

    UNION ALL

    SELECT
        s.total_count,
        -- 配对成功则左指针右移，否则保持不动
        CASE
            WHEN h.pound + l.pound <= 200 THEN p.left_ptr + 1
            ELSE p.left_ptr
        END AS left_ptr,
        -- 无论是否配对成功，右指针始终左移一位（最重样本已处理）
        p.right_ptr - 1 AS right_ptr,
        CONCAT('G_', p.left_ptr, '_', p.right_ptr) AS group_no,
        next_s.sample_id,
        next_s.pound
    FROM pair_recursion p
    JOIN sorted_samples h 
        ON h.heavy_idx = p.right_ptr
    LEFT JOIN sorted_samples l 
        ON l.light_idx = p.left_ptr
    JOIN sorted_samples next_s 
        ON next_s.heavy_idx = p.right_ptr - 1
    WHERE p.left_ptr <= p.right_ptr
)
-- 输出最终配对结果
SELECT
    group_no,
    GROUP_CONCAT(sample_id SEPARATOR ',') AS matched_sample_ids,
    SUM(pound) AS group_total_pound
FROM pair_recursion
WHERE group_no IS NOT NULL
GROUP BY group_no
ORDER BY group_no;

方案优势 #

• 没有多层CTE的冗余嵌套，逻辑完全和人工配对流程对齐，方便调试校验
• 仅做一次窗口函数排序+递归遍历，没有大表自连接的笛卡尔积问题，数据量大时性能也有保障
• 输出结果直接给出每组总重量，可以快速校验是否符合200磅的上限要求

内容的提问来源于stack exchange，提问作者Rémy G