使用 JOIN 计算 mySQL 出现的次数 [英] mySQL count occurrences with JOIN

问题描述

我的活动系统有一个标签系统，我想创建一个标签云".

我有活动，可以有多个类别".

表结构如下:

**Event_Categories**(商店标签/类别)|身份证 |姓名 |+---++ 1 |运动 |+ 2 |慈善|+ 3 |other_tag |**Events_Categories**(链接表)|event_id |event_category_id |+------------------------------++ 1 |1 |+ 2 |2 |+ 3 |1 |+ 3 |2 |

总结:

事件 ID 1 ->运动事件 ID 2 ->慈善机构事件 ID 3 ->体育、慈善

我想返回以下内容:

<代码>|标签名称 |发生|+------------+------------+|运动 |2 ||社会 |2 |

other_tag - 没有实际返回，因为它出现了 0 次

谢谢！:)

解决方案

这会起作用:

SELECT c.name AS tag_name, COUNT(ec.event_id) AS 出现次数FROM Event_Categories cINNER JOIN Events_Categories ec ON c.id = ec.event_category_id按 c.id 分组

如果要包含出现次数为 0 的类别，请将 INNER JOIN 更改为 LEFT JOIN

I have a tagging system for my events system I would like to create a 'tag cloud'.

I have Events, which can have multiple 'categories'.

Here's the table structure:

**Event_Categories** (Stores Tags / Categories)
| id  | name      |
+-----------------+
+ 1   | sport     |
+ 2   | charity   |
+ 3   | other_tag |


**Events_Categories** (Linking Table)
| event_id  | event_category_id |
+-------------------------------+
+    1      |       1           |   
+    2      |       2           |   
+    3      |       1           |   
+    3      |       2           |   

Summary:

Event ID 1 -> Sport
Event ID 2 -> Charity
Event ID 3 -> Sport, Charity

I'd like to return the following:

| tag_name  | occurrences |
+-----------+-------------+
|  sport    |     2       |
|  society  |     2       |

other_tag - Not actually returned, as it has 0 occurrences

Thanks! :)

解决方案

this will work:

SELECT c.name AS tag_name, COUNT(ec.event_id) AS occurrences 
FROM Event_Categories c 
INNER JOIN Events_Categories ec ON c.id = ec.event_category_id 
GROUP BY c.id

change the INNER JOIN to LEFT JOIN if you want to include categories with 0 occurances

这篇关于使用 JOIN 计算 mySQL 出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！