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 itemids 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 itemids where there is an id for all three ids.
但是由于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屋!