MySQL相交结果 [英] Mysql intersect results

问题描述

我正在尝试执行类似PHP array_intersect的操作.我有下表

I'm trying to do something like the PHP array_intersect. I have the following table

CREATE TABLE `recipes_ingredients_items` (
  `id` INTEGER(11) DEFAULT NULL,
  `itemid` INTEGER(11) DEFAULT NULL,
  `orderby` TINYINT(4) NOT NULL,
  KEY `id` (`id`),
  KEY `itemid` (`itemid`)
)ENGINE=MyISAM
AVG_ROW_LENGTH=47 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';  

我需要一个查询，该查询将为我提供包含ID 2,72,64作为唯一itemid的所有结果.问题是一个id在itemid中可能不止一次存在，即itemid 600可能有3行与ID相关的ID 2、100、2.
我的想法是用x数o子查询进行查询，以返回结果并执行类似PHP array_intersect的操作，从而返回具有所有这些ID的itemid.

I need a query that will get me all the results that contain say id 2,72,64 as a distinct itemid. The thing is that an id may exist more than once in an itemid, i.e itemid 600 may have 3 rows with ids 2,100,2 asociated with it.
My idea was to have a query with say x number o subqueries returning results and doing something like the PHP array_intersect, thus returning the itemids that have all these ids.

SELECT DISTINCT itemid  FROM recipes_ingredients_items
WHERE 
id IN (SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 71) 
AND 
id IN (SELECT DISTINCT  itemid FROM recipes_ingredients_items WHERE id = 2)
AND 
id IN (SELECT DISTINCT  itemid FROM recipes_ingredients_items WHERE id = 22)

这就是我得到的，而且100％错误.

This is what I got and it is 100% wrong.

推荐答案

通常，您将对每个 id 的 itemid ‍s进行交集:

Normally you would do an intersection of the itemid‍s of each id:

(SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 71)
INTERSECT
(SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 2)
INTERSECT
(SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 22)

这只会选择所有三个 id ‍s都有一个 id 的 itemid ‍s.

This will only select those itemid‍s where there is an id for all three id‍s.

但是由于MySQL不支持INTERSECT，因此您需要使用内部联接:

But since MySQL does not support INTERSECT, you need to use inner joins:

SELECT DISTINCT itemid FROM recipes_ingredients_items
INNER JOIN (SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 71) a USING (itemid)
INNER JOIN (SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 2) b USING (itemid)
INNER JOIN (SELECT DISTINCT itemid FROM recipes_ingredients_items WHERE id = 22) c USING (itemid)

这篇关于MySQL相交结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！