如何在MySQL的左连接中获取关联行的计数？

I have two tables, a vehicle table with columns:

我有两张桌子，一张带有柱子的车辆桌子：

• id
• ID
• stock
• 股票
• year
• 年
• make
• 使
• model
• 模型

and an images table with columns:

和一个包含列的图像表：

• id
• ID
• vehicle_id
• vehicle_id
• name
• 名称
• caption
• 标题
• default tinyint(1)
• 默认的tinyint（1）

I am trying to list the vehicle's information, its default image, and a total count of images the vehicle has. Currently I am using the following SELECT statement:

我试图列出车辆的信息，默认图像和车辆图像的总数。目前我使用以下SELECT语句：

SELECT vehicle.id, vehicle.stock, vehicle.year,
    vehicle.make, vehicle.model, images.name,
    COUNT(images.id)
FROM vehicle
LEFT JOIN images
ON vehicle.id = images.vehicle_id

I initially was using:

我最初使用的是：

ON vehicle.id = images.vehicle_id AND images.default = 1

but then the images count would only be 1 or 0 depending if there was a default image in the database. I have tried using UNION and other SELECT statements but I am still unable to get a proper result. Do I need to use two SELECT statements or is there another way to handle it with JOIN or UNION?

但是，根据数据库中是否有默认图像，图像计数只会是1或0。我已经尝试使用UNION和其他SELECT语句，但我仍然无法得到正确的结果。我是否需要使用两个SELECT语句，或者是否有其他方法可以使用JOIN或UNION来处理它？

3 个解决方案

#1

SELECT 
    `vehicle`.`id`, 
    `vehicle`.`stock`, 
    `vehicle`.`year`, 
    `vehicle`.`make`, 
    `vehicle`.`model`, 
    `images`.`name`,
    (
        SELECT COUNT(*) 
        FROM `images` 
        WHERE `vehicle_id` = `vehicle`.`id`
    ) AS `image_count`
FROM `vehicle`
LEFT JOIN `images`
ON `images`.`vehicle_id` = `vehicle`.`id`
WHERE `images`.`default`

#2

In the way the anser suggests, you get repeated values of "vehicle". A better way, is to group results. Try without the JOIN :

在anser建议的方式中，你得到重复的“车辆”值。更好的方法是对结果进行分组。尝试没有加入：

SELECT 
    `vehicle`.`id`, 
    `vehicle`.`stock`, 
    `vehicle`.`year`, 
    `vehicle`.`make`, 
    `vehicle`.`model`, 
    `images`.`name`,
    (
        SELECT COUNT(*) 
        FROM `images` 
        WHERE `vehicle_id` = `vehicle`.`id`
    ) AS `image_count`
FROM `vehicle`

WHERE `images`.`default`

#3

Let me make it clear for everyone!

Task: Print 3 columns table:

任务：打印3列表：

1. Vehicles (titles) from vehicles table.
2. 车辆（车辆）的车辆（标题）。
3. Amount of comments for each vehicle from comments table.
4. 评论表中每辆车的评论数量。
5. Amount of images for each vehicle from images table.
6. 图像表中每辆车的图像数量。

Expected output (just an example):

预期输出（仅举例）：

+----------------------+----------------+--------------+
|        title         | comments_count | images_count |
+----------------------+----------------+--------------+
| BMW X6               |             35 |            9 |
| Audi A6              |              3 |            5 |
| Volkswagen Passat B6 |             78 |            6 |
| Volkswagen Passat B5 |            129 |            4 |
+----------------------+----------------+--------------+

Solution:

解：

SELECT 
    vehicles.title,
    (SELECT COUNT(*) FROM comments WHERE vehicles.id = comments.vehicle_id) AS comments_count,
    (SELECT COUNT(*) FROM images WHERE vehicles.id = images.vehicle_id) AS images_count
FROM vehicles