sql - MySQL GROUP_CONCAT + IN() = 缺失数据 :-(

Question

例子:

表:方框

boxID  color
 01     red
 02     blue
 03     green

表格:boxHas

boxID  has
 01     apple
 01     pear
 01     grapes
 01     banana
 02     lime
 02     apple
 02     pear
 03     chihuahua
 03     nachos
 03     baby crocodile

我想查询每个框的内容，并返回一个包含每个 ID、颜色和连接每个框内容的列的表，因此我使用:

SELECT box.boxID, box.color,
GROUP_CONCAT(DISTINCT boxHas.has SEPARATOR ", ") AS 内容
FROM 框
LEFT JOIN boxHas ON box.boxID=boxHas.boxID
WHERE boxHas.has IN ('apple','pear')
GROUP BY box.boxID
按 box.boxID 排序

我得到了下表的结果:

boxID  color  contents
 01     red    apple, pear
 02     blue   apple, pear

我的问题是:为什么不在 contents 列中列出所有 has 值？为什么我的 WHERE 语句也裁剪了我的 GROUP_CONCAT？

我以为我要得到的 table 是:

boxID  color  contents
 01     red    apple, banana, grapes, pear
 02     blue   apple, lime, pear

虽然我想根据 WHERE 语句限制我的 boxID 结果，但我不想想限制内容 有效框的字段。 :-/

帮忙吗？

Answer 1

您必须使用 HAVING 子句而不是 WHERE:

SELECT box.boxID
     , box.color
     , GROUP_CONCAT(DISTINCT boxHas.has SEPARATOR ", ") AS contents
  FROM box
  LEFT JOIN boxHas 
    ON box.boxID=boxHas.boxID
 GROUP BY box.boxID
HAVING SUM(boxHas.has IN ('apple','pear')) >= 2
 ORDER BY box.boxID