连接两个表，将一个列与多个值匹配 [英] Join two tables, matching a column with multiple values

问题描述

我正在尝试获得与某些自定义参数匹配的产品. 因此，我必须使用三个表-产品，参数和参数项.

I am trying to get a product matching some custom parameters. So I have to three tables - products, parameters and parametersitems.

产品表:

CREATE TABLE `products` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT
  `Title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `Content` longtext COLLATE utf8_unicode_ci NOT NULL,
  `Price` float(10,2) unsigned NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

参数表:

CREATE TABLE `parameters` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Label` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

参数项目表:

CREATE TABLE `parametersitems` (
  `ProductID` int(10) unsigned NOT NULL DEFAULT '0',
  `ParameterID` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ProductID`,`ParameterID`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

所以我的问题是我如何才能只获得与所有参数匹配的产品.

So my question is how can I get only the products matching all the parameters.

我能想到的唯一方法是两次连接参数项表. 例如，这是一个查询以获取与两个参数匹配的产品:

The only way I could think of is joining the parameteritems table couple of times. For example, here is a query to get the products matching two parameters:

SELECT 
    products.*
FROM 
    products 

INNER JOIN 
    parametersitems AS paritems1
    ON  
        paritems1.ItemID = products.ID 
        AND paritems1.ParameterID = 7

INNER JOIN 
    parametersitems AS paritems2
    ON  
        paritems2.ItemID = products.ID 
        AND paritems2.ParameterID = 11

我唯一关心的是，如果选择了更多参数，则SELECT查询的速度将越来越慢. 那么，有没有更好的方法来解决这个问题呢?

My only concern is that the SELECT query will get slower and slower if there more parameters selected. So is there a better way to handle this problem?

谢谢

推荐答案

select p.*
from products p
inner join (
    select ItemID
    from parametersitems 
    where ParameterID in (7, 11)
    group by ItemID
    having count(distinct ParameterID) = 2
) pm on p.ID = pm.ItemID

这篇关于连接两个表，将一个列与多个值匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！