MySQL左连接多个列对 [英] MySQL left join multiple column pairs

问题描述

给出下表(products_filter):

Given the following table (products_filter):

如何执行SELECT ... FROM products LEFT JOIN products_filter ...的方式，使其仅返回具有所有指定的(filter_id，filter_value)对的产品.

How can I do a SELECT ... FROM products LEFT JOIN products_filter ... in such a way that it only returns products which have ALL the specified (filter_id,filter_value) pairs.

示例:对于(filter_id，filter_value)=(1,1)，(3,0)，它应仅返回ID为90001的乘积，因为它匹配两个值.

Example: for (filter_id, filter_value) = (1,1),(3,0) it should only return the product with id 90001, because it matches both values.

推荐答案

正如您只说过的那样，您想要具有所需过滤器属性的PRODUCTS值...我将结果限制为product.*

As you only said you wanted the PRODUCTS values having the desired filter attributes... I've limited results to just product.*

以下查询使用内联视图，其中按产品ID列出了不同过滤器的数量.然后，外部where子句使用filter_ID的唯一计数(以防万一产品可能存在重复的过滤器).

The below query uses an inline view with the count of distinct filters by product ID. The outer where clause then uses the distinct count (in case duplicate filters could exist for a product) of the filter_IDs.

where子句中的#始终应与内联视图中where子句对的集合数匹配.

The # in the where clause should always match the number of where clause paired sets in the inline view.

您的样本数据表明配对的集合可能是所有过滤器的子集.这样可以确保所需产品的每个过滤器对(或更多对)都存在.

Your sample data indicated that the paired sets could be a subset of all filters. so this ensures each filter pair (or more) exists for the desired product.

SELECT p.* 
FROM products p
LEFT JOIN (SELECT product_ID, count(Distinct filter_ID) cnt
           FROM products_Filter
           WHERE (Filter_ID = 1 and filter_value = 1)
              or (Filter_ID = 3 and filter_value = 0)
           GROUP BY Product_ID) pf
   on P.Product_ID = PF.Product_ID
 WHERE pf.cnt = 2

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