SQL查询：满足指定条件的披萨组合元组列表

嘿，我来帮你搞定这个SQL查询需求！先把每个要求对应的逻辑拆解清楚，再写出完整的查询语句：

核心逻辑拆解 #

• 选3种指定披萨：从给定的Hawaiian、Pizza3、Pepperoni中选3种不同的，还要关联对应餐厅的售价
• 总价限制：三种披萨在同一家餐厅的售价总和≤80美元
• 披萨的受众要求：每种选中的披萨至少被客户A、B、C里的某一位喜欢
• 客户的喜好要求：A、B、C每位都得喜欢选中的3种披萨里至少2种

完整SQL语句 #

SELECT 
    s1.rname AS 餐厅名称,
    s1.pizza AS pizza1,
    s2.pizza AS pizza2,
    s3.pizza AS pizza3,
    (s1.price + s2.price + s3.price) AS 总成本
FROM 
    Sells s1
JOIN 
    Sells s2 ON s1.rname = s2.rname AND s1.pizza < s2.pizza
JOIN 
    Sells s3 ON s2.rname = s3.rname AND s2.pizza < s3.pizza
WHERE 
    -- 限定从指定的3种披萨中选择
    s1.pizza IN ('Hawaiian', 'Pizza3', 'Pepperoni')
    AND s2.pizza IN ('Hawaiian', 'Pizza3', 'Pepperoni')
    AND s3.pizza IN ('Hawaiian', 'Pizza3', 'Pepperoni')
    -- 确保三个披萨完全不同（通过排序避免重复组合）
    AND s1.pizza != s2.pizza AND s2.pizza != s3.pizza AND s1.pizza != s3.pizza
    -- 总成本不超过80美元
    AND (s1.price + s2.price + s3.price) <= 80
    -- 每种披萨至少被A、B、C中的一位喜欢
    AND EXISTS (SELECT 1 FROM Likes l WHERE l.pizza = s1.pizza AND l.cname IN ('A', 'B', 'C'))
    AND EXISTS (SELECT 1 FROM Likes l WHERE l.pizza = s2.pizza AND l.cname IN ('A', 'B', 'C'))
    AND EXISTS (SELECT 1 FROM Likes l WHERE l.pizza = s3.pizza AND l.cname IN ('A', 'B', 'C'))
    -- 客户A至少喜欢其中2种披萨
    AND (SELECT COUNT(DISTINCT pizza) FROM Likes l WHERE l.cname = 'A' AND l.pizza IN (s1.pizza, s2.pizza, s3.pizza)) >= 2
    -- 客户B至少喜欢其中2种披萨
    AND (SELECT COUNT(DISTINCT pizza) FROM Likes l WHERE l.cname = 'B' AND l.pizza IN (s1.pizza, s2.pizza, s3.pizza)) >= 2
    -- 客户C至少喜欢其中2种披萨
    AND (SELECT COUNT(DISTINCT pizza) FROM Likes l WHERE l.cname = 'C' AND l.pizza IN (s1.pizza, s2.pizza, s3.pizza)) >= 2
ORDER BY 
    餐厅名称, 总成本;

关键细节说明 #

• 用s1.pizza < s2.pizza < s3.pizza的关联条件，能避免重复的披萨组合（比如顺序不同的同一种三元组只会返回一次）
• EXISTS子句用来快速验证单种披萨是否被目标客户群中的至少一人喜欢，效率比IN子句更高
• 针对每个客户的子查询统计他们喜欢的选中披萨数量，确保满足“至少喜欢2种”的要求
• 所有关联都限定在同一家餐厅（s1.rname = s2.rname = s3.rname），保证计算的是同一家餐厅的总价

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