本文介绍了如何在MySQL的左联接中获得相关行的计数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个表,一个带有列的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语句还是使用JOINUNION处理它的另一种方法?

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的左联接中获得相关行的计数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-12 19:55