我有三张桌子,也就是。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)的那些过滤器名称?
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句