使用MySQL列出属于一个类别表的多个表中的公共数据

Question

我有3个表。这->类别，博客，课程

类别表

| category_id | category_name |
|      1      |  php          |
|      2      |  python       |
|      3      |  javascript   |

博客表

| blog_id | blog_title | blog_category_id |
|    1    | trial 1    |         1        |
|    2    | trial 2    |         2        |
|    3    | trial 3    |         1        |
|    4    | trial 4    |         2        |
|    5    | trial 5    |         3        |
|    6    | trial 6    |         1        |
|    7    | trial 7    |         3        |

航线表

| course_id | course_title | course_category_id |
|    1      | trial 1      |         1          |
|    2      | trial 2      |         3          |
|    3      | trial 3      |         2          |
|    4      | trial 4      |         1          |
|    5      | trial 5      |         1          |
|    6      | trial 6      |         1          |
|    7      | trial 7      |         3          |

输出：

| category_name | TotalCount |
|    php        |     7      |
|    python     |     3      |
|    javascript |     4      |

我如何列出这样的输出MySQL。请。。。。

Answer 1

使用UNION ALL从blog和course表中获取所有类别ID，然后JOIN返回category表以获取类别名称。CCD_ 6和CCD_。

结果如下：

SELECT COUNT(category.category_id), category_name FROM category 
JOIN (
SELECT blog_category_id AS category_id FROM blog 
UNION ALL 
SELECT course_category_id AS category_id FROM course
) a ON 
(category.category_id = a.category_id)
GROUP BY category.category_id;