MySql选择查询组合结果

Question

嗨，我有2个表customers和customer_items。现在，在第二个表中的两个表中都有一个customer_id，每个客户可以有多个项目，因此表可以是这样的

id           |    item
----------------------------
1501         |    pillow
1501         |    blanket
1501         |    others
1502         |    pillow
1502         |    blanket
1502         |    others

现在如何使用mysql查询选择既有枕头又有毯子的客户

这是我的最后一种方法

select custlist.id FROM customers custlist LEFT JOIN customer_items custitems ON custitems.id=custlist.id WHERE (custitems.items='pillow' AND custitems.items='blanket') UNION ALL

Answer 1

这是一个Relational Division问题。

SELECT  a.customer_ID
FROM    customers a
        INNER JOIN customer_items b
            ON a.customer_ID = b.customer_ID
WHERE   b.item IN ('pillow', 'blanket')
GROUP   BY a.customer_ID
HAVING  COUNT(*) = 2

• SQL of Relational Division

如果item对于每个customer_ID都不是唯一，则需要使用DISTINCT关键字来仅计算唯一的记录。

SELECT  a.customer_ID
FROM    customers a
        INNER JOIN customer_items b
            ON a.customer_ID = b.customer_ID
WHERE   b.item IN ('pillow', 'blanket')
GROUP   BY a.customer_ID
HAVING  COUNT(DISTINCT b.item) = 2

Answer 2

JW对上述解决方案的细微变化：

SELECT  a.customer_ID
FROM    customers a
INNER JOIN (SELECT customer_ID, item FROM customer_items WHERE item = 'pillow' GROUP BY customer_ID) PillowCheck
ON a.customer_ID = PillowCheck.customer_ID
INNER JOIN (SELECT customer_ID, item FROM customer_items WHERE item = 'blanket' GROUP BY customer_ID) BlanketCheck
ON a.customer_ID = PillowCheck.customer_ID

Answer 3

尝试此查询

SELECT 
   a.id 
FROM 
   customer_items a
INNER JOIN
   customer_items b
ON
   a.id= b.id and 
   a.item = 'PILLOW' AND 
   b.item='blanket'

如果您需要客户名称，则只需连接customer表即可。如果客户有多个枕头或毯子，则添加distinct