Question

我在公共卫生和社会福利部和MySQL实施了一个套头系统,其表如下:

表:博客

tag_id
tag_name

表:博客_assoc

tag_assoc_id
article_id
tag_id

Table: blog_articles

article_id
article_title
article_content

I have them all linked using the Many to Many relationship (reference to something similar: How to store tags in MySQL tags, one field in total or one filed for each tag?)

What I m trying to do now is make a MySQL query that selects all the tags from blog_tags that have actually been referenced by the articles in blog_articles.

So far, I know that "HAVING" is something I might use…but I m not sure how? Any ideas or suggestions would be most welcome. Thanks!

Answer 1

This will get a tag name as well as a count of articles that tag has been used.

SELECT
    `blog_tags`.`tag_name`,
    count(*) AS `blog_article_count`
FROM `blog_tags`
    JOIN `blog_tags_assoc`
        ON `blog_tags`.`tag_id`=`blog_tags_assoc`.`tag_id`
    JOIN `blog_articles`
        ON `blog_articles`.`article_id`=`blog_tags_assoc`.`article_id`
GROUP BY `blog_tags`.`tag_name`

Answer 2

如果你只想知道在文章中使用了哪些标签,那么你就可以这样做:

SELECT DISTINCT tag_id, tag_name
FROM blog_tags
INNER JOIN blog_tags_assoc USING ( tag_id );

友情链接