MySQL联接查询（可能是两个内部联接） [英] MySQL Join Query (possible two inner joins)

问题描述

我目前有以下内容：

桌镇：

• id


• 名称


• 区域

表供应商：

• id


• 名称


• town_id

以下查询返回每个镇的供应商数量：

The below query returns the number of suppliers for each town:

SELECT t.id, t.name, count(s.id) as NumSupplier
FROM Town t 
INNER JOIN Suppliers s ON s.town_id = t.id 
GROUP BY t.id, t.name

我现在希望在查询中引入另一个表， Supplier_vehicles。供应商可以拥有许多车辆：

I now wish to introduce another table in to the query, Supplier_vehicles. A supplier can have many vehicles:

表Supplier_vehicles：

Table Supplier_vehicles:

• id


• supplier_id


• vehicle_id

现在，NumSupplier字段需要返回每个具有给定的vehicle_id（IN条件）的城镇的供应商数量：

Now, the NumSupplier field needs to return the number of suppliers for each town that have any of the given vehicle_id (IN condition):

以下查询将简单地带回具有以下任何一个的供应商：给定的车辆ID：

The following query will simply bring back the suppliers that have any of the given vehicle_id:

SELECT * FROM Supplier s, Supplier_vehicles v WHERE s.id = v.supplier_id AND v.vehicle_id IN (1, 4, 6)

我需要将其集成到第一个查询中，以便它返回

I need to integrate this in to the first query so that it returns the number of suppliers that have any of the given vehicle_id.

推荐答案

SELECT t.id, t.name, count(s.id) as NumSupplier
FROM Town t 
INNER JOIN Suppliers s ON s.town_id = t.id 
WHERE s.id IN (SELECT sv.supplier_id
               FROM supplier_vehicles sv 
               WHERE sv.vehicle_id IN (1,4,6))
GROUP BY t.id, t.name

$ b分组$ b

或者您可以执行 INNER JOIN （因为您的供应商加入是INNER，但这将删除没有供应商的城镇）。 c $ c> COUNT（s.id）到 COUNT（DISTINCT s.id）

Or you could do an INNER JOIN (as your supplier join is INNER, but this will remove towns with no suppliers with those vehicles) and change the COUNT(s.id) TO COUNT(DISTINCT s.id)

这篇关于MySQL联接查询（可能是两个内部联接）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！