问题描述
我有两个表,一个带有列的vehicle
表:
I have two tables, a vehicle
table with columns:
-
id
-
stock
-
year
-
make
-
model
id
stock
year
make
model
和带有列的images
表:
-
id
-
vehicle_id
-
name
-
caption
-
default tinyint(1)
id
vehicle_id
name
caption
default tinyint(1)
我正在尝试列出车辆的信息,其默认图像以及该车辆具有的图像总数.目前,我正在使用以下SELECT
语句:
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 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
我最初使用的是:
ON vehicle.id = images.vehicle_id AND images.default = 1
,但是根据数据库中是否有默认图像,图像计数将仅为1或0.我已经尝试使用UNION
和其他SELECT
语句,但是仍然无法获得正确的结果.我需要使用两个SELECT
语句还是使用JOIN
或UNION
处理它的另一种方法?
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
?
推荐答案
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`
这篇关于如何在MySQL的左联接中获得相关行的计数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!