如何统计和连接两个MySQL表？

Question

我想写出租车的次数，我只是不能得到它的工作，我有两张桌子。一个叫vehicle，另一个叫rental。

在租赁是所有的“租赁”去当你预订一辆车，所有的车辆存储在车辆。这是我所做的几乎可行的查询。

SELECT vehicle.id,COUNT(rental.vid) as rented,IFNULL(rental.vid,0) as nothing, vehicle.make as make, vehicle.model as model, vehicle.regnr as regnr, vehicle.color as color, vehicle.state as state, vehicle.imgurl as img, vehicle.description as description, vehicle.id 
FROM rental,vehicle 
WHERE vid = vehicle.id GROUP BY vid 

，并打印出这一点：

不具有一个值（从未被租用），其余不存在，我已经尝试了很多不同的方式与IFNULL但没有得到任何地方。

Answer 1

Select从Vehicle表和Left Join到Rental表。这将包括从未被租用他们的计数（rental.vid）为0的车辆：

SELECT vehicle.id 
,COUNT(rental.vid) as rented 
, vehicle.make as make 
, vehicle.model as model 
, vehicle.regnr as regnr 
, vehicle.color as color 
, vehicle.state as state 
, vehicle.imgurl as img 
, vehicle.description as description 
FROM vehicle 
left join rental on vid = vehicle.id 
GROUP BY vehicle.id 

Here is a simplified example

隐含的加入你在你的例子是相当于inner join。通过左连接，您可以从源表中选择所需的所有行。如果与您要加入的表格匹配，它们也会显示。这是将数据追加到源表的好方法。