我有三个表，即。 tb_filters、tb_products 和 tb_products_to_filters。这些表的结构以及一些虚拟数据由下式给出:

tb_filters:

CREATE TABLE IF NOT EXISTS `tb_filters`
(
    `filter_id` INT (11) AUTO_INCREMENT PRIMARY KEY,
    `filter_name` VARCHAR (255)
);

INSERT INTO `tb_filters`
(`filter_name`)
VALUES ('USB'),
('High Speed'),
('Wireless'),
('Ethernet');

tb_products:

CREATE TABLE IF NOT EXISTS `tb_products`
(
    `product_id` INT (11) AUTO_INCREMENT PRIMARY KEY,
    `product_name` VARCHAR (255)
);

INSERT INTO `tb_products`
(`product_name`)
VALUES ('Ohm precision shunt resistor'),
('Orchestrator Libraries'),
('5cm scanner connection'),
('Channel isolated digital'),
('Network Interface Module');

tb_products_to_filters:

CREATE TABLE IF NOT EXISTS `tb_products_to_filters`
(
    `id` INT (11) AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT (11),
    `filter_id` INT (11)
);

INSERT INTO `tb_products_to_filters`
(`product_id`, `filter_id`)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 3),
(1, 3);

通过查看上面的“tb_products_to_filters”表，我需要的查询是:

当通过页面上的复选框选择过滤器 ID = 1 和 3 时，必须从数据库中提取属于过滤器 ID 1 和过滤器 ID 3 的所有产品。在这种情况下，id 为 1 的产品应该会出现。

其次，当只选中一个过滤器(比如 id = 3)时，则应提取所有属于该 id 的产品。在这种情况下，将出现产品 ID 1、3 和 4。

如果选择过滤器 id 2，则只会出现一个 id = 2 的产品。

如果选择过滤器组合(2 和 3)，则不会出现任何产品，因为没有属于它们的产品。

要达到上述目标，如何编写查询？

请注意，我想包括列:product_id、product_name、filter_id 和 filter_name 以在表结果集中显示数据。

编辑:

当检查过滤器 ID 1 和 3 时，输出应该与下面匹配:

编辑 2:

我正在尝试以下查询以在选中过滤器 1 和 3 时获取结果:

SELECT `p`.`product_id`, `p`.`product_name`, 
GROUP_CONCAT(DISTINCT `f`.`filter_id` ORDER BY `f`.`filter_id` SEPARATOR ', ') AS filter_id, GROUP_CONCAT(DISTINCT `f`.`filter_name` ORDER BY `f`.`filter_name` SEPARATOR ', ') AS filter_name 
FROM `tb_products` AS `p` INNER JOIN `tb_products_to_filters` AS `ptf` 
ON `p`.`product_id` = `ptf`.`product_id` INNER JOIN `tb_filters` AS `f` 
ON `ptf`.`filter_id` = `f`.`filter_id` GROUP BY `p`.`product_id` 
HAVING GROUP_CONCAT(DISTINCT `ptf`.`filter_id` SEPARATOR ', ') = ('1,3') 
ORDER BY `p`.`product_id`

但不幸的是，它返回一个空集。为什么？

最佳答案

您可以将 HAVING 子句与 GROUP_CONCAT 一起使用:

SELECT t.product_id,tp.product_name,
       GROUP_CONCAT(t.filter_id) as filter_id,
       GROUP_CONCAT(tb.filter_name) as filter_name
FROM tb_products_to_filters t
INNER JOIN tb_filters tb ON(t.filter_id = tb.filter_id)
INNER JOIN tb_products tp ON(t.product_id = tp.product_id)
WHERE t.filter_id IN(1,3)
GROUP BY t.product_id
HAVING COUNT(distinct t.filter_id) = 2

您可以随意调整。请注意，放置在 IN() 中的参数数量应与 COUNT(..) = X

编辑:

在获取这些列时，GROUP_CONCAT 中需要一个 DISTINCT 关键字，否则所有过滤器都会出现在列表中。我试了一下

SELECT t.product_id,tp.product_name,
       GROUP_CONCAT(DISTINCT t.filter_id ORDER BY `t`.`filter_id` SEPARATOR ', ') as filter_id,
       GROUP_CONCAT(DISTINCT tb.filter_name ORDER BY tb.filter_name SEPARATOR ', ') as filter_name
FROM tb_products_to_filters t
INNER JOIN tb_filters tb ON(t.filter_id = tb.filter_id)
INNER JOIN tb_products tp ON(t.product_id = tp.product_id)
WHERE t.filter_id IN(1,3)
GROUP BY t.product_id
HAVING COUNT(distinct t.filter_id) = 2

但所有过滤器名称(以太网、高速、USB、无线)仍然出现在列表中。如何只列出字符串中对应过滤器 id (1, 3) 的过滤器名称？

关于php - 如何在MySQL中查询产品和过滤器之间的多对多关系？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39014757/