如何将产品计数到多个过滤器

本文介绍了如何将产品计数到多个过滤器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

更新

我喜欢制作这样的产品过滤器模块 Online Shop ，当您点击其中一个过滤器以计算其他过滤器中的当前产品

例如，如果这是前端并且它们是复选框并且它们是未选中的 atm

大小 (group_id)10m (option_id: 52) (21 个产品)20m (option_id: 51) (1 个产品)颜色(group_id)绿色 (option_id: 49) (22 个产品)黑色 (option_id: 38) (1 个产品)

如果用户选择 10m (option_id: 52) 过滤器计数器应该变成这样

大小 (group_id)10m (option_id: 52) (21 个产品)20m (option_id: 51) (1)颜色(group_id)绿色 (option_id: 49) (2)黑色 (option_id: 38) (0)

我做了一张表，只是为了计算我保留 group_id、option_id、product_id、category_id、manufacturer_id 的位置

我查询要过滤的产品数量，但问题是我无法返回同一类别中的其他过滤器计数器(option_id:38)和同一类别中的所有制造商的制造商计数器，有什么想法吗?

SELECT sd.*, COUNT(sd.product_id) FROM filter_counter sd WHERE sd.product_id IN (SELECT c.product_id FROM filter_counter c WHERE c.option_id IN (52) AND c.category_id = 127) AND sd.category_id = 127 GROUP BY sd.option_id

创建表`filter_counter`(`id` int(11) 非空，`group_id` int(11) 非空，`option_id` int(11) 非空，`product_id` int(11) 非空，`category_id` int(11) 非空，`manufacturer_id` int(11) 非空) 引擎=MyISAM 默认字符集=utf8;插入`filter_counter`(`id`、`group_id`、`option_id`、`product_id`、`category_id`、`manufacturer_id`)值(1, 33, 52, 5124, 65, 36),(2, 33, 52, 5124, 127, 36),(3, 33, 52, 5125, 65, 36),(4, 33, 52, 5125, 127, 36),(5, 33, 52, 5138, 65, 36),(6, 33, 52, 5138, 127, 36),(7, 33, 52, 5141, 65, 36),(8, 33, 52, 5141, 127, 36),(9, 33, 52, 5146, 65, 36),(10, 33, 52, 5146, 127, 36),(11, 33, 52, 5147, 65, 36),(12, 33, 52, 5147, 127, 36),(13, 33, 52, 5148, 65, 36),(14, 33, 52, 5148, 127, 36),(15, 33, 52, 5149, 65, 36),(16, 33, 52, 5149, 127, 36),(17, 33, 52, 5150, 65, 36),(18, 33, 52, 5150, 127, 36),(19, 33, 52, 5151, 65, 36),(20, 33, 52, 5151, 127, 36),(21, 33, 52, 5152, 65, 36),(22, 33, 52, 5152, 127, 36),(23, 33, 52, 5153, 65, 36),(24, 33, 52, 5153, 127, 36),(25, 33, 52, 5154, 65, 36),(26, 33, 52, 5154, 127, 36),(27, 33, 52, 5155, 65, 36),(28, 33, 52, 5155, 127, 36),(29, 33, 52, 5156, 65, 36),(30, 33, 52, 5156, 127, 36),(31, 33, 52, 5157, 65, 36),(32, 33, 52, 5157, 127, 36),(33, 33, 52, 7042, 65, 38),(34, 33, 52, 7042, 127, 38),(35, 33, 52, 7048, 65, 38),(36, 33, 52, 7048, 127, 38),(37, 33, 52, 7124, 65, 0),(38, 33, 52, 7124, 127, 0),(39, 32, 49, 7185, 65, 0),(40, 32, 49, 7185, 127, 0),(41, 32, 49, 7517, 65, 39),(42, 32, 49, 7517, 127, 39),(43, 32, 49, 7518, 65, 39),(44, 32, 49, 7518, 127, 39),(45, 32, 49, 7538, 65, 39),(46, 32, 49, 7538, 127, 39),(47, 32, 49, 7657, 65, 39),(48, 32, 49, 7657, 127, 39),(49, 32, 49, 7658, 65, 39),(50, 32, 49, 7658, 127, 39),(51, 32, 49, 7797, 65, 21),(52, 32, 49, 7797, 127, 21),(53, 32, 49, 7798, 65, 21),(54, 32, 49, 7798, 127, 21),(55, 32, 49, 7799, 65, 21),(56, 32, 49, 7799, 127, 21),(57, 32, 49, 7800, 65, 21),(58, 32, 49, 7800, 127, 21),(59, 32, 49, 7801, 65, 21),(60, 32, 49, 7801, 127, 21),(61, 32, 49, 7802, 65, 21),(62, 32, 49, 7802, 127, 21),(63, 32, 49, 7803, 65, 21),(64, 32, 49, 7803, 127, 21),(65, 32, 49, 7804, 65, 21),(66, 32, 49, 7804, 127, 21),(67, 32, 49, 7805, 65, 21),(68, 32, 49, 7805, 127, 21),(69, 32, 49, 7806, 65, 21),(70, 32, 49, 7806, 127, 21),(71, 32, 49, 7807, 65, 21),(72, 32, 49, 7807, 127, 21),(73, 32, 49, 7808, 65, 21),(74, 32, 49, 7808, 127, 21),(75, 32, 49, 7809, 65, 21),(76, 32, 49, 7809, 127, 21),(77, 32, 49, 7810, 65, 21),(78, 32, 49, 7810, 127, 21),(79, 29, 38, 7811, 65, 21),(80, 29, 38, 7811, 127, 21),(81, 32, 49, 8020, 65, 21),(82, 32, 49, 8020, 127, 21),(83, 33, 52, 8020, 65, 21),(84, 33, 52, 8020, 127, 21),(85, 32, 49, 8021, 65, 21),(86, 32, 49, 8021, 127, 21),(87, 33, 51, 8021, 65, 21),(88, 33, 51, 8021, 127, 21),(89, 33, 52, 8021, 65, 21),(90、33、52、8021、127、21)；

我的数据库和查询示例

解决方案

例如，如果这是前端并且它们是复选框并且它们是未选中的 atm

大小 (group_id)10m (option_id: 52) (21 个产品)20m (option_id: 51) (1 个产品)颜色(group_id)绿色 (option_id: 49) (22 个产品)黑色 (option_id: 38) (1 个产品)

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,计数(DISTINCT CASE WHEN option_id = 51 THEN product_id END)p51，COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,计数(DISTINCT CASE WHEN option_id = 38 THEN product_id END)p38从过滤器计数器；

如果用户选择 10m (option_id: 52) 过滤器计数器应该变成这样

大小 (group_id)10m (option_id: 52) (21 个产品)20m (option_id: 51) (1)颜色(group_id)绿色 (option_id: 49) (2)黑色 (option_id: 38) (0)

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,计数(DISTINCT CASE WHEN option_id = 51 THEN product_id END)p51，COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,计数(DISTINCT CASE WHEN option_id = 38 THEN product_id END)p38FROM filter_counterJOIN ( SELECT DISTINCT product_idFROM filter_counterWHERE option_id IN (52) ) filter1 USING (product_id);

小提琴

如果用户检查同一组中的其他过滤器，则将下一个值添加到条件中.例如，如果他检查与 option_id = 52 在同一组中的 option_id = 51 然后过滤子查询变为

JOIN ( SELECT DISTINCT product_idFROM filter_counterWHERE option_id IN (52, 51) ) filter1 USING (product_id)

如果用户检查另一个组中的附加过滤器，则添加附加的 filterX 子查询.例如，如果他检查 option_id = 49 然后添加到查询的下一个子查询:

JOIN ( SELECT DISTINCT product_idFROM filter_counterWHERE option_id IN (49) ) filter2 USING (product_id)

有没有办法让输出变成每行一个?

SELECT options.option_id,COUNT(DISTINCT CASE WHEN filter_counter.option_id = options.option_idTHEN product_idEND) option_countFROM filter_counterCROSS JOIN ( SELECT DISTINCT option_idFROM filter_counter ) 选项JOIN ( SELECT DISTINCT product_idFROM filter_counterWHERE option_id IN (52) ) filter1 USING (product_id)GROUP BY options.option_id;

fiddle

UPDATED

I like to make product filter module like this one Online Shop , when you click one of the filters to count current products in other filters

For example if this is frontend and their are checkboxes and their are UNchecked atm

Size (group_id) 10m (option_id: 52) (21 products) 20m (option_id: 51) (1 product) Color (group_id) Green (option_id: 49) (22 products) Black (option_id: 38) (1 product)

If a user select 10m (option_id: 52) the filter counter should become like this

Size (group_id) 10m (option_id: 52) (21 products) 20m (option_id: 51) (1) Color (group_id) Green (option_id: 49) (2) Black (option_id: 38) (0)

I made one table only for counting where i keep group_id,option_id,product_id,category_id,manufacturer_id

I make query that count products to filter but problem is that i cant return other filter counter (option_id: 38) in same category and manufacturer counter for all manufacturer in same category , any ideas how to make it ?

SELECT sd.*, COUNT(sd.product_id) FROM filter_counter sd WHERE sd.product_id IN (SELECT c.product_id FROM filter_counter c WHERE c.option_id IN (52) AND c.category_id = 127) AND sd.category_id = 127 GROUP BY sd.option_id

CREATE TABLE `filter_counter` ( `id` int(11) NOT NULL, `group_id` int(11) NOT NULL, `option_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, `category_id` int(11) NOT NULL, `manufacturer_id` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `filter_counter` (`id`, `group_id`, `option_id`, `product_id`, `category_id`, `manufacturer_id`) VALUES (1, 33, 52, 5124, 65, 36), (2, 33, 52, 5124, 127, 36), (3, 33, 52, 5125, 65, 36), (4, 33, 52, 5125, 127, 36), (5, 33, 52, 5138, 65, 36), (6, 33, 52, 5138, 127, 36), (7, 33, 52, 5141, 65, 36), (8, 33, 52, 5141, 127, 36), (9, 33, 52, 5146, 65, 36), (10, 33, 52, 5146, 127, 36), (11, 33, 52, 5147, 65, 36), (12, 33, 52, 5147, 127, 36), (13, 33, 52, 5148, 65, 36), (14, 33, 52, 5148, 127, 36), (15, 33, 52, 5149, 65, 36), (16, 33, 52, 5149, 127, 36), (17, 33, 52, 5150, 65, 36), (18, 33, 52, 5150, 127, 36), (19, 33, 52, 5151, 65, 36), (20, 33, 52, 5151, 127, 36), (21, 33, 52, 5152, 65, 36), (22, 33, 52, 5152, 127, 36), (23, 33, 52, 5153, 65, 36), (24, 33, 52, 5153, 127, 36), (25, 33, 52, 5154, 65, 36), (26, 33, 52, 5154, 127, 36), (27, 33, 52, 5155, 65, 36), (28, 33, 52, 5155, 127, 36), (29, 33, 52, 5156, 65, 36), (30, 33, 52, 5156, 127, 36), (31, 33, 52, 5157, 65, 36), (32, 33, 52, 5157, 127, 36), (33, 33, 52, 7042, 65, 38), (34, 33, 52, 7042, 127, 38), (35, 33, 52, 7048, 65, 38), (36, 33, 52, 7048, 127, 38), (37, 33, 52, 7124, 65, 0), (38, 33, 52, 7124, 127, 0), (39, 32, 49, 7185, 65, 0), (40, 32, 49, 7185, 127, 0), (41, 32, 49, 7517, 65, 39), (42, 32, 49, 7517, 127, 39), (43, 32, 49, 7518, 65, 39), (44, 32, 49, 7518, 127, 39), (45, 32, 49, 7538, 65, 39), (46, 32, 49, 7538, 127, 39), (47, 32, 49, 7657, 65, 39), (48, 32, 49, 7657, 127, 39), (49, 32, 49, 7658, 65, 39), (50, 32, 49, 7658, 127, 39), (51, 32, 49, 7797, 65, 21), (52, 32, 49, 7797, 127, 21), (53, 32, 49, 7798, 65, 21), (54, 32, 49, 7798, 127, 21), (55, 32, 49, 7799, 65, 21), (56, 32, 49, 7799, 127, 21), (57, 32, 49, 7800, 65, 21), (58, 32, 49, 7800, 127, 21), (59, 32, 49, 7801, 65, 21), (60, 32, 49, 7801, 127, 21), (61, 32, 49, 7802, 65, 21), (62, 32, 49, 7802, 127, 21), (63, 32, 49, 7803, 65, 21), (64, 32, 49, 7803, 127, 21), (65, 32, 49, 7804, 65, 21), (66, 32, 49, 7804, 127, 21), (67, 32, 49, 7805, 65, 21), (68, 32, 49, 7805, 127, 21), (69, 32, 49, 7806, 65, 21), (70, 32, 49, 7806, 127, 21), (71, 32, 49, 7807, 65, 21), (72, 32, 49, 7807, 127, 21), (73, 32, 49, 7808, 65, 21), (74, 32, 49, 7808, 127, 21), (75, 32, 49, 7809, 65, 21), (76, 32, 49, 7809, 127, 21), (77, 32, 49, 7810, 65, 21), (78, 32, 49, 7810, 127, 21), (79, 29, 38, 7811, 65, 21), (80, 29, 38, 7811, 127, 21), (81, 32, 49, 8020, 65, 21), (82, 32, 49, 8020, 127, 21), (83, 33, 52, 8020, 65, 21), (84, 33, 52, 8020, 127, 21), (85, 32, 49, 8021, 65, 21), (86, 32, 49, 8021, 127, 21), (87, 33, 51, 8021, 65, 21), (88, 33, 51, 8021, 127, 21), (89, 33, 52, 8021, 65, 21), (90, 33, 52, 8021, 127, 21);

My database and query example

解决方案

For example if this is frontend and their are checkboxes and their are UNchecked atm

Size (group_id) 10m (option_id: 52) (21 products) 20m (option_id: 51) (1 product) Color (group_id) Green (option_id: 49) (22 products) Black (option_id: 38) (1 product)

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52, COUNT(DISTINCT CASE WHEN option_id = 51 THEN product_id END) p51, COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49, COUNT(DISTINCT CASE WHEN option_id = 38 THEN product_id END) p38 FROM filter_counter;

If a user select 10m (option_id: 52) the filter counter should become like this

Size (group_id) 10m (option_id: 52) (21 products) 20m (option_id: 51) (1) Color (group_id) Green (option_id: 49) (2) Black (option_id: 38) (0)

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52, COUNT(DISTINCT CASE WHEN option_id = 51 THEN product_id END) p51, COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49, COUNT(DISTINCT CASE WHEN option_id = 38 THEN product_id END) p38 FROM filter_counter JOIN ( SELECT DISTINCT product_id FROM filter_counter WHERE option_id IN (52) ) filter1 USING (product_id);

fiddle

If user checks additional filter in the same group then the next value is added into the condition. For example, if he checks option_id = 51 which is in the same group with option_id = 52 then filtering subquery become

JOIN ( SELECT DISTINCT product_id FROM filter_counter WHERE option_id IN (52, 51) ) filter1 USING (product_id)

If user checks additional filter in another group then additional filterX subquery added. For example, if he checks option_id = 49 then the next subquery added to the query:

JOIN ( SELECT DISTINCT product_id FROM filter_counter WHERE option_id IN (49) ) filter2 USING (product_id)

---

is there any way output to become one per line ?

SELECT options.option_id, COUNT(DISTINCT CASE WHEN filter_counter.option_id = options.option_id THEN product_id END) option_count FROM filter_counter CROSS JOIN ( SELECT DISTINCT option_id FROM filter_counter ) options JOIN ( SELECT DISTINCT product_id FROM filter_counter WHERE option_id IN (52) ) filter1 USING (product_id) GROUP BY options.option_id;

fiddle