mysql - 如何获取包含类别类型和组类型两个术语的产品 ID

Question

如何获取category type和group type这两个term中包含的product id

我有一张表 ec_terms

和 ec_terms_product

如何获得同时包含别名 best-offers 和 premium-cars 的产品 ID？

在这种情况下，产品 ID 应为 4。

试过了，但没有得到任何结果

SELECT *, count(ec_terms_product.product_id) AS coutp 
FROM ec_terms_product 
LEFT JOIN ec_terms 
ON ec_terms_product.term_id = ec_terms.id 
WHERE 1=1 
AND (ec_terms.alias IN ('best-offers') AND ec_terms.alias IN ('premium-cars'))
GROUP BY ec_terms_product.term_id ,ec_terms_product.product_id

你能帮我吗？

Answer 1

只需使用group by 和having:

SELECT etp.product_id
FROM ec_terms_product etp JOIN
     ec_terms et 
     ON etp.term_id = et.id 
GROUP BY etp.product_id 
HAVING SUM(et.alias = 'best-offers') > 0 AND
       SUM(at.alias = 'premium-cars') > 0;

请注意，LEFT JOIN 不是必需的。 LEFT JOIN 只会增加查询的开销。

一个更有效的方法是:

SELECT etp.product_id
FROM ec_terms_product etp JOIN
     ec_terms et 
     ON etp.term_id = et.id 
WHERE et.alias IN ('best-offers', 'premium-cars')
GROUP BY etp.product_id 
HAVING COUNT(*) = 2;