MySQL：返回所有条件(但不满足其他条件)为真的所有结果

有了EXISTS，你就会得到你想要的结果

EXist是判断具有所需值的值是否存在的最快方法.

在poacage_id、项目和大小上建立索引将提高性能

SELECT 
    p.id, p.name, c.item, c.size
FROM
    Packages p
        LEFT JOIN
    Contents c ON (c.packageid = p.id)
WHERE

(SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'square' AND size = 'A'))
AND (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'circle' AND size = 'B')) 
  AND  (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'triangle' AND size = 'C')) 
  AND (SELECT COUNT(*) FROM Contents WHERE packageid = p.id) = 3;

SELECT
p.id, p.name, c.item, c.size
FROM
    Packages p
        LEFT JOIN
    Contents c ON (c.packageid = p.id)
WHERE

(SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'square' AND size = 'A'))
AND (SELECT 
        1
   FROM
        Contents
    WHERE
        packageid = p.id AND (item = 'circle' AND size = 'B')) 
  AND (SELECT COUNT(*) FROM Contents WHERE packageid = p.id) = 2;

fiddle个