MySQL相交结果 [英] Mysql intersect results

查看:46
本文介绍了MySQL相交结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试执行类似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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆