MySQL-我可以结合这2条SQL语句吗?结合JOIN和AVG? [英] MySQL - Can I combine these 2 SQL statements? Combine JOIN and AVG?

问题描述

我可以结合这2条SQL语句吗?当前正在运行2个查询.试图收紧一点.

Can I combine these 2 SQL statements? Currently running 2 queries. Trying to tighten up a bit.

第一个:

SELECT * FROM (`cars`) 
JOIN `brands` ON `brands`.`br_id` = `cars`.`brand_id`
WHERE `cars`.`id` = '185707'

第二个:

SELECT ROUND(AVG(rating)) as avg_rating
FROM car_ratings WHERE car_id = 185707 

推荐答案

您可以使用group by:

select cars.*,
       brands.*, 
       round(avg(car_ratings.rating)) as avg_rating
from   (cars
    inner join brands on brands.br_id = cars.brand_id)
    left join car_ratings on car_ratings.car_id = cars.id
where  cars.id = 185707
group by cars.id

请注意，这是一个MySQL扩展到标准SQL ；在标准SQL中，您需要在group by子句中列出所有选定的字段.

Note that this is a MySQL extension to standard SQL; in standard SQL you would need to list all of the selected fields in the group by clause.

这篇关于MySQL-我可以结合这2条SQL语句吗?结合JOIN和AVG?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！