Mysql join 4个表可以在一个查询中完成吗

Question

我的数据库

category_group(id,name)
category(id,name,cat_group_id)
topic(id,name,cat_id)
comment(id,name,topic_id)

我想要得到:

Category Group 1
=====================
Category 1
Count topic | Count comment
-------------------
Category 2
Count topic | Count comment


Category Group 2
=====================
Category 3
Count topic | Count comment
-------------------
Category 4
Count topic | Count comment

我只能处理很多不同的查询，但我认为这不是一个好的做法。

Answer 1

如果所有表都严格相关，则可以使用内连接

    select a.*, b.*, c.*, d.*
    from category as a
    inner join category_group as b on a.cat_group_id = b id
    inner join topic as c on a.id = c.cat_id
    inner join comment as d.topic_id = c.id

其他需要使用左连接的地方

那么对于你的情况，你可以这样做

    select b.name,  a.name, count(d.*) as count_commect, count(c.*) as count_topic
    from category as a
    inner join category_group as b on a.cat_group_id = b id
    inner join topic as c on a.id = c.cat_id
    inner join comment as d.topic_id = c.id
    group by a.name, b.name
    order by a.name, b.name