MySQL多列求和不超过指定阈值的查询实现求助

1. 筛选单个符合条件的食品 #

如果仅需找出单份（100克）食品的营养成分不超过阈值，直接用基础WHERE条件即可。假设表名为foods，字段包含food_id（食品ID）、name（食品名称）、fats（脂肪）、proteins（蛋白质）、carbohydrates（碳水化合物）、calories（热量）：

SELECT food_id, name, fats, proteins, carbohydrates, calories
FROM foods
WHERE fats <= 100
  AND proteins <= 150
  AND carbohydrates <= 10
  AND calories <= 2000;

将SQL中的阈值数值替换为用户输入的参数即可。

2. 筛选多个食品的组合（总和符合阈值） #

如果需要找出多份食品的组合，使得各营养成分总和不超过阈值，这属于组合优化问题，可通过MySQL递归CTE实现。注意：当食品数据量较大时，递归会产生大量中间结果，性能会明显下降，仅建议在小数据量场景使用。

示例SQL（阈值为脂肪100、蛋白质150、碳水化合物10、热量2000）：

WITH RECURSIVE food_combinations AS (
    -- 初始行：单个食品的组合
    SELECT
        food_id AS combo_food_ids,
        name AS combo_names,
        fats AS total_fats,
        proteins AS total_proteins,
        carbohydrates AS total_carbs,
        calories AS total_calories,
        1 AS food_count
    FROM foods
    WHERE fats <= 100
      AND proteins <= 150
      AND carbohydrates <= 10
      AND calories <= 2000

    UNION ALL

    -- 递归：往现有组合中添加新食品（通过ID大小避免重复组合）
    SELECT
        CONCAT(fc.combo_food_ids, ', ', f.food_id),
        CONCAT(fc.combo_names, ', ', f.name),
        fc.total_fats + f.fats,
        fc.total_proteins + f.proteins,
        fc.total_carbs + f.carbohydrates,
        fc.total_calories + f.calories,
        fc.food_count + 1
    FROM food_combinations fc
    JOIN foods f ON f.food_id > SUBSTRING_INDEX(fc.combo_food_ids, ', ', -1)
    WHERE fc.total_fats + f.fats <= 100
      AND fc.total_proteins + f.proteins <= 150
      AND fc.total_carbs + f.carbohydrates <= 10
      AND fc.total_calories + f.calories <= 2000
)
-- 输出所有符合条件的组合，可按需排序
SELECT combo_names, total_fats, total_proteins, total_carbs, total_calories, food_count
FROM food_combinations
ORDER BY food_count DESC, total_calories DESC;

说明 #

• 递归初始部分先筛选出所有单个符合条件的食品；
• 递归部分通过添加ID更大的食品，避免生成重复组合（如[食品A,食品B]和[食品B,食品A]视为同一组合）；
• 每次添加食品时都会校验总和是否超出阈值；
• 若允许重复选择同一种食品，去掉JOIN条件中的f.food_id > SUBSTRING_INDEX(...)即可。

注意事项 #

• 若食品表数据量超过50条，建议限制递归深度（比如添加food_count <= 5的条件），或在应用层实现组合筛选逻辑，避免查询超时；
• 可根据需求调整排序规则，比如优先显示热量最高的组合。

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