mysql - 从四个表中选择计数

Question

我有四张 table

1. 公司(字段:id、名称)
2. 城市(字段:id、名称)
3. 类别(字段:id、名称)
4. city_category_company(字段:company_id、city_id、category_id)

我需要计算城市页面上每个类别的公司数量。

现在，我在如此丑陋的循环中做这件事

$q = mysqli_query($db, "SELECT * FROM `categories`");
while ($r = mysqli_fetch_assoc($q)) {
    $r2 = mysqli_fetch_assoc(
        mysqli_query($db, "SELECT COUNT(`company_id`) AS `num` FROM `city_category_company` WHERE `city_id` = '{$city['id']}' AND `category_id` = '{$r['id']}'")
    );
}

但是如果我有很多类别，它就会在一页(50 个或更多)上对数据库进行很多查询。请帮我优化查询

Answer 1

试试这个:

SELECT c.id, c.name, COUNT(ccc.company_id) AS num
FROM city_category_company ccc
INNER JOIN categories c ON ccc.category_id = cc.id
WHERE ccc.city_id={city_id}
GROUP BY c.id