Как получить количество связанных строк в левом соединении в MySQL?

У меня есть две таблицы, таблица vehicle со столбцами:

  • id
  • stock
  • year
  • make
  • model

и таблица images со столбцами:

  • id
  • vehicle_id
  • name
  • caption
  • default tinyint(1)

Я пытаюсь перечислить информацию о транспортном средстве, его изображение по умолчанию и общее количество изображений, которые есть у транспортного средства. В настоящее время я использую следующий оператор 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

Я изначально использовал:

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

но тогда количество изображений будет только 1 или 0 в зависимости от того, есть ли в базе данных изображение по умолчанию. Я пытался использовать UNION и другие операторы SELECT, но все еще не могу получить правильный результат. Нужно ли мне использовать два оператора SELECT или есть другой способ справиться с этим с помощью JOIN или UNION?


mysql sql join select subquery
person Cris McLaughlin    schedule 30.04.2009    source источник

Ответы (3)


arrow_upward
6
arrow_downward

Как предлагает ответ, вы получаете повторяющиеся значения «транспортное средство». Лучший способ — сгруппировать результаты. Попробуйте без JOIN:

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`
person gdm    schedule 08.10.2013
comment
Этот ответ лучше, чем принятый ответ, @giuseppe попробуйте отредактировать принятый ответ, возможно, автор изменит его. - person Vitaliy Z; 30.04.2014

arrow_upward
4
arrow_downward

Позвольте мне сделать это ясно для всех!

Задача: распечатать таблицу из 3 столбцов:

  1. Транспортные средства (названия) из таблицы транспортные средства.
  2. Количество комментариев для каждого автомобиля из таблицы comments.
  3. Количество изображений для каждого автомобиля из таблицы images.

Ожидаемый результат (просто пример):

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

Решение:

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
person Erikas    schedule 13.08.2017

arrow_upward

arrow_downward

person    schedule
comment
Я согласен. Это будет правильный способ сделать это. Я могу придумать другой способ (с использованием подзапроса вместо прямой таблицы изображений), но это хакерский и отвратительный (и просто для удовольствия). Это правильный способ сделать это. - person MBCook; 30.04.2009
comment
+1 за это. Я немного почистил его, чтобы избавиться от свитка. - person TheTXI; 30.04.2009
comment
Лучше ли использовать COUNT(id) вместо COUNT(*), чтобы я не выбирал все столбцы? - person Cris McLaughlin; 30.04.2009
comment
В данной ситуации разницы нет. В общем, COUNT(*) следует использовать, когда вы имеете в виду количество строк. COUNT(id) означает, сколько идентификаторов, которые не являются нулевыми, что может быть или не быть гораздо более сложным вопросом. - person chaos; 30.04.2009
comment
если mysql поддерживает сообщение CTE i, это решение - person DForck42; 30.04.2009
comment
Я считаю, что есть некоторые преимущества в производительности для использования COUNT(*) вместо COUNT(id) при запросе таблиц MyISAM, поскольку я считаю, что они хранят общее количество строк. Однако это, вероятно, можно использовать только в том случае, если в вашем запросе нет никаких условий. - person pr1001; 15.08.2009