如何编写SQL查询三类餐食数据满足总卡路里要求且标题不重复

报错原因 #

你执行第二个查询抛出1241错误，是因为SELECT关键字后的子查询属于标量子查询，语法要求只能返回1行1列的单个值，你用SELECT *返回了id、title等多个字段，不符合语法规则。

你最早写的GROUP BY dishType的写法属于非标准SQL，仅在MySQL关闭ONLY_FULL_GROUP_BY模式时能执行，返回的每行数据完全随机，既没有校验三餐总卡路里，也没有做标题去重，无法满足需求。

实现方案 #

我们可以先分别取出三类餐食的候选数据，交叉匹配找到符合总卡路里要求、标题互不重复的随机组合，再拆成3行返回。
你可以直接使用下面的查询语句，兼容MySQL 8.0及以上版本：

-- 替换为用户输入的每日目标总卡路里
SET @target_total_cal = 2000;

WITH breakfast_candidates AS (
    SELECT id, title, calories, dishType
    FROM livewell.recipes
    WHERE dishType = 'breakfast' AND calories >= 500 -- 不需要单条最低卡路里要求可以删掉该条件
),
lunch_candidates AS (
    SELECT id, title, calories, dishType
    FROM livewell.recipes
    WHERE dishType = 'lunch' AND calories >= 500
),
main_candidates AS (
    SELECT id, title, calories, dishType
    FROM livewell.recipes
    WHERE dishType = 'main course' AND calories >= 500
)
SELECT result.* FROM (
    -- 找到符合条件的随机一组三餐组合
    SELECT
        b.id b_id, b.title b_title, b.calories b_cal,
        l.id l_id, l.title l_title, l.calories l_cal,
        m.id m_id, m.title m_title, m.calories m_cal
    FROM breakfast_candidates b
    CROSS JOIN lunch_candidates l
    CROSS JOIN main_candidates m
    WHERE
        -- 总卡路里不超过目标值
        (b.calories + l.calories + m.calories) <= @target_total_cal
        -- 三个标题互不重复
        AND b.title <> l.title
        AND b.title <> m.title
        AND l.title <> m.title
    ORDER BY RAND()
    LIMIT 1
) valid_group
-- 把一行组合拆分为3行返回
JOIN LATERAL (
    SELECT b_id id, b_title title, b_cal calories, 'breakfast' dishType
    UNION ALL
    SELECT l_id id, l_title title, l_cal calories, 'lunch' dishType
    UNION ALL
    SELECT m_id id, m_title title, m_cal calories, 'main course' dishType
) result;

如果你使用的是不支持LATERAL语法的低版本MySQL，可以用下面的兼容写法：

SET @target_total_cal = 2000;

SELECT id, title, calories, dishType FROM livewell.recipes WHERE id = (
    SELECT b.id
    FROM livewell.recipes b
    INNER JOIN livewell.recipes l 
        ON l.dishType = 'lunch' AND l.calories >= 500 AND b.title <> l.title
    INNER JOIN livewell.recipes m 
        ON m.dishType = 'main course' AND m.calories >= 500 AND b.title <> m.title AND l.title <> m.title
    WHERE b.dishType = 'breakfast' AND b.calories >= 500 
        AND (b.calories + l.calories + m.calories) <= @target_total_cal
    ORDER BY RAND()
    LIMIT 1
)
UNION ALL
SELECT id, title, calories, dishType FROM livewell.recipes WHERE id = (
    SELECT l.id
    FROM livewell.recipes b
    INNER JOIN livewell.recipes l 
        ON l.dishType = 'lunch' AND l.calories >= 500 AND b.title <> l.title
    INNER JOIN livewell.recipes m 
        ON m.dishType = 'main course' AND m.calories >= 500 AND b.title <> m.title AND l.title <> m.title
    WHERE b.dishType = 'breakfast' AND b.calories >= 500 
        AND (b.calories + l.calories + m.calories) <= @target_total_cal
    ORDER BY RAND()
    LIMIT 1
)
UNION ALL
SELECT id, title, calories, dishType FROM livewell.recipes WHERE id = (
    SELECT m.id
    FROM livewell.recipes b
    INNER JOIN livewell.recipes l 
        ON l.dishType = 'lunch' AND l.calories >= 500 AND b.title <> l.title
    INNER JOIN livewell.recipes m 
        ON m.dishType = 'main course' AND m.calories >= 500 AND b.title <> m.title AND l.title <> m.title
    WHERE b.dishType = 'breakfast' AND b.calories >= 500 
        AND (b.calories + l.calories + m.calories) <= @target_total_cal
    ORDER BY RAND()
    LIMIT 1
);

性能说明 #

你当前每类餐食只有100条数据，交叉匹配最多产生100万次运算，性能完全足够。如果后续数据量变大，可以给dishType、calories字段加索引进一步优化查询速度。

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