MySQL表之间的关系

Question

嘿，我有一个问题。我有两张 table 。表1和表2。表1:

id | car_numbers |  type  |      model   | type_id
1        3           bmw       <model1>       1
2        5           bmw       <model2>       1
3        2         mercedes    <model1>       2
4        4         mercedes    <model2>       2
5        1         chevrolet   <model1>       3

表2:

id | price | type_id
1     100       1
2     200       1
3     300       2 
4     400       2 
5     500       3

我想要的是显示“类型”、“汽车编号”以及每种汽车类型的平均价格。基本上我想要的这两个表之间的结果是:

  type    | car_numbers | average_price
  bmw            8             150
mercedes         6             350
chevrolet        1             500

我怎样才能做到这一点？我知道我必须与两个表中常见的 type_id 相关，但我该怎么做？

Answer 1

SELECT type, car_numbers, AVG(price) AS average_price
FROM (SELECT type_id, type, SUM(car_numbers) AS car_numbers
      FROM table1
      GROUP BY type_id) AS t1
JOIN table2 AS t2 ON t1.type_id = t2.type_id
GROUP BY type

DEMO