mySQL计数发生与JOIN [英] mySQL count occurrences with JOIN
本文介绍了mySQL计数发生与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'.
以下是表格结构:
**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 |
摘要:
Event ID 1 -> Sport
Event ID 2 -> Charity
Event ID 3 -> Sport, Charity
我想返回以下内容:
| tag_name | occurrences |
+-----------+-------------+
| sport | 2 |
| society | 2 |
other_tag - 实际上没有返回,因为它有0次出现
other_tag - Not actually returned, as it has 0 occurrences
谢谢! :)
推荐答案
这将有效:
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
将 INNER JOIN
更改为 LEFT JOIN
如果您想包含0次出现的类别
change the INNER JOIN
to LEFT JOIN
if you want to include categories with 0 occurances
这篇关于mySQL计数发生与JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文