多对多MySQL表中的计数和多个分组

Question

我想知道整天，我无法完成它。

我有一个简单的测试表，以news系统为例。我们有news,tags和categories。 News可以有许多tags和一个category。我需要的是统计每个category中每个tag下有多少个news。例如，我们可以在政治类别下带有通用标签的4 news，在科学类别下带有通用标签的2 news。

我的表是这样的：

news: 
    - news_id 
    - category_id 
    - title 

categories: 
    - category_id 
    - category_name 

tags: 
    - tag_id 
    - tag_name 

news_tags: 
    - news_id 
    - tag_id 

下面是一个简单的脑图，以澄清什么，我需要： http://i.stack.imgur.com/6ySiJ.png

下面是一个查询，我没有成功尝试：

SELECT *, COUNT(n.news_id) AS news_count FROM news AS n 
LEFT JOIN categories AS c ON n.category_id = c.category_id 
LEFT JOIN news_tags AS tn ON n.news_id = tn.news_id 
LEFT JOIN tags AS t ON tn.tag_id = t.tag_id 
GROUP BY t.tag_id, c.category_id; 

Answer 1

您的查询无误我的行为。 运行查询时会出现什么错误/为什么它不成功？

这是我尝试设置您的情况：

mysql> select * from news; 
+---------+-------------+------------------------+ 
| news_id | category_ID | title     | 
+---------+-------------+------------------------+ 
|  1 |   1 | politics and general 1 | 
|  2 |   1 | politics and general 2 | 
|  3 |   1 | politics and general 3 | 
|  4 |   1 | politics and general 4 | 
|  5 |   2 | science and general 1 | 
|  6 |   2 | science and general 2 | 
|  7 |   2 | science and funny 1 | 
+---------+-------------+------------------------+ 

mysql> select * from tags; 
+--------+----------+ 
| tag_id | tag_name | 
+--------+----------+ 
|  1 | general | 
|  2 | funny | 
+--------+----------+ 

mysql> select * from news_tags; 
+---------+--------+ 
| news_id | tag_id | 
+---------+--------+ 
|  1 |  1 | 
|  2 |  1 | 
|  3 |  1 | 
|  4 |  1 | 
|  5 |  1 | 
|  6 |  1 | 
|  7 |  2 | 
+---------+--------+ 

mysql> select * from categories; 
+-------------+---------------+ 
| category_id | category_name | 
+-------------+---------------+ 
|   1 | politics  | 
|   2 | science  | 
+-------------+---------------+ 

查询的结果：

+---------+-------------+------------------------+-------------+---------------+---------+--------+--------+----------+------------+ 
| news_id | category_ID | title     | category_id | category_name | news_id | tag_id | tag_id | tag_name | news_count | 
+---------+-------------+------------------------+-------------+---------------+---------+--------+--------+----------+------------+ 
|  1 |   1 | politics and general 1 |   1 | politics  |  1 |  1 |  1 | general |   4 | 
|  5 |   2 | science and general 1 |   2 | science  |  5 |  1 |  1 | general |   2 | 
|  7 |   2 | science and funny 1 |   2 | science  |  7 |  2 |  2 | funny |   1 | 
+---------+-------------+------------------------+-------------+---------------+---------+--------+--------+----------+------------+ 

然而，它没有任何意义SELECT *因为你的标签总计数/类别，像title这样的东西在聚合时没有意义。

您可以试试：

SELECT c.category_name, t.tag_name, COUNT(n.news_id) AS news_count FROM news AS n 
LEFT JOIN categories AS c ON n.category_id = c.category_id 
LEFT JOIN news_tags AS tn ON n.news_id = tn.news_id 
LEFT JOIN tags AS t ON tn.tag_id = t.tag_id 
GROUP BY t.tag_id, c.category_id; 

要获取：

+---------------+----------+------------+ 
| category_name | tag_name | news_count | 
+---------------+----------+------------+ 
| politics  | general |   4 | 
| science  | general |   2 | 
| science  | funny |   1 | 
+---------------+----------+------------+