为拥有两种以上产品的供应商获取产品总量

Question

我需要获得拥有两个以上产品的供应商的产品数量和供应商名称。

给定表格产品和供应商

________________________________products__________________________________            
|product_id|product_name|supplier_id|category_id|unit              |price| 
| 1        | Chais      | 1         | 1         |10 boxes x 20 bags|18.00|
| 14       | Tofu       | 6         | 7         |40 - 100 g pkgs.  |23.25|
________________________________suppliers_________________________________     
|supplier_id|supplier_name            |contact_name    |address                  |city     |postal_code|country|phone         |
| 1         |Exotic Liquid            |Charlotte Cooper|49 Gilbert St.           |London   |EC1 4SD    |UK     |[phone]|
| 10        |Refrescos Americanas LTDA|Carlos Diaz     |Av. das Americanas 12.890|São Paulo|5442       |Brazil |088-981 55 42 |

尝试的内容：

SELECT products.product_id,
suppliers.supplier_name
FROM products
INNER JOIN suppliers ON products.supplied_id=suppliers.supplied_id
WHERE products.product_id >2

Answer 1

您只需要连接表并使用聚合。

SELECT      s.supplier_name
,count(p.product_id) as Total_products 
FROM        suppliers s
INNER JOIN  products p ON p.supplied_id=s.supplied_id
GROUP BY    s.supplier_name 
HAVING      count(p.product_id) > 2