MySQL专家-需要“相交"帮助 [英] mySQL experts - need help with 'intersect'

问题描述

我知道mySQL 5.x不支持INTERSECT，但这似乎正是我所需要的.

I know that mySQL 5.x does not support INTERSECT, but that seems to be what I need.

表A:产品(p_id)

Table A: Products (p_id)

表B:Prod_cats(cat_id)-类别信息(名称，说明等)

Table B: Prod_cats (cat_id) - category info (name, description, etc)

表C:prod_2cats(p_id，cat_id)-多对多

Table C: prod_2cats (p_id, cat_id) - many to many

prod_2cats拥有已分配给产品(A)的许多(1个或更多)类别.

prod_2cats holds the many (1 or more) categories that have been assigned to Products (A).

进行查询/过滤器查找(用户交互)，并且需要能够在多个类别中选择符合所有条件的产品.

Doing a query/filter lookup, (user interactive) and need to be able to select across multiple categories the products that meet ALL the criteria.

例如: -分配给X类的80种产品 -分配给Y类的50种产品 -但是只将10个产品(相交)分配给cat X和cat Y

Ex: - 80 products assigned to Category X - 50 products assigned to Category Y - but only 10 products (intersect) are assigned to BOTH cat X AND cat Y

此sql适用于一个类别:

This sql works for one category:

选择*从产品中p_show ='Y'和p_id输入(从prods_2cats中选择p_id作为PC，在PC.cat_id =.$ cat_id."

SELECT * FROM products WHERE p_show='Y' AND p_id IN ( SELECT p_id FROM prods_2cats AS PC WHERE PC.cat_id =" . $cat_id ."

<-$ cat_id是从查询表单传递来的经过清理的var.

<-$cat_id is sanitized var passed from query form .

我似乎找不到办法说给我猫A和猫B的相交"并取回子集(根据我的示例，有10条记录)

I can't seem to find the means to say ' give me the intersect of cat A and cat B' and get back the subset (10 records, from my example)

帮助！

推荐答案

应用集合论中交集的定义.因此，如果X与Y相交，则给出集合Z，则对于Z中的每个x都认为x在X中，x在Y中.

Apply the definition of intersection from set theory. So if X intersect Y gives set Z, then for each x in Z it holds that x is in X and x is in Y.

类似

SELECT * FROM products WHERE p_show='Y' AND p_id IN 
( SELECT p_id FROM prods_2cats AS PC WHERE PC.cat_id =X) AND p_id IN
( SELECT p_id FROM prods_2cats AS PC WHERE PC.cat_id =Y)

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