php - tricky mysql query counting records in several categories

Question

这是我的数据结构:有项目，每个项目最多可以有 3 个类别

categories
id   name
-----------------------
1    cat1
2    cat2
3    cat3

items
id  name     category1  category2  category3
-----------------------------------------------
1   item1    1          2          3
2   item2    1          2       
3   item3    2          1          3
4   item4    2          3        
5   item5    3          1          2

期望的输出:

cat   cat_name   total_items
-----------------------------------------------
1     cat1       4
2     cat2       5
3     cat3       4

我能实现它的唯一方法是使用 CONCAT_WS 为项目定义一个 View ，它将所有 3 个类别转换为分隔字符串，然后使用 FIND_IN_SET 查询它但是我认为应该有更好/更快的方法。有什么想法吗？

谢谢

Answer 1

您可以使用 where category1、category2 或 category3 执行选择子查询来计算类别中的项目。

SELECT c.*, 
    (SELECT COUNT(id) FROM items i WHERE i.category1 = c.id OR i.category2 = c.id OR i.category3 = c.id) as total_items 
    FROM categories c