sql - 如何计算第二个表中给定 id 的命中次数

Question

我有 3 张桌子，比如“品牌”、“威士忌”和“国家”

品牌

brand_id   brand_name      brand_country_id
1          Example brand   1
2          Brand 2         2

威士忌酒

whisky_id   whisky_brand_id
1           2
2           2
3           1
4           2

国家

country_id  country_nicename
1           Poland
2           Germany

我有 SQL：

SELECT
    brands.brand_id,
    brands.brand_name,
    brands.brand_country_id,
    country.country_id,
    country.country_niename
FROM
    brands
LEFT JOIN
    country
ON
    brands.brand_country_id = country.country_id
LEFT JOIN
    whisky
ON
    brands.brand_id = whisky.whisky_brand_id

我想要像这样的数据

brand_id brand_name country_id country_nicename no.ofWhisky
2        Brand2     1          Germany          3    

但我不知道如何在这个查询中计算威士忌的数量：/有人可以帮忙吗？谢谢 ：）

标签： sqlcountleft-join

Answer 1

您只需要使用group by&进行聚合count()：

SELECT brands.brand_id, brands.brand_name, country.country_id, country.country_niename, 
       count(whisky_id) as no.ofWhisky
FROM brands LEFT JOIN 
     country 
     ON brands.brand_country_id = country.country_id LEFT JOIN 
     whisky 
     ON brands.brand_id = whisky.whisky_brand_id
GROUP BY brands.brand_id, brands.brand_name, country.country_id, country.country_niename;