mysql - 选择属性和最大值

Question

我有通过外键连接的 MySql 表，如下

Person(Driver_id, Name, Address)

Car(Reg_no, Model, Year)

Owns(Driver_id, Reg_no)

Accident(Report_no, Date, Location)

Participated(Report_no, Driver_no, Reg_no, Damage_amt)

Car Values:
KA35F2845 | Fiat Punto   | 2013 |
KA25Y1878 | Audi A4      | 2015 |

Participated Values:
1 | 101| KA35F2845  |      10000 |
2 | 102| KA25Y1878  |      30000 |

Requirement: I want to display Model of the Car having highest Damage_amt using Join.

下面是我尝试过的代码:

select c.model from car c join (select reg_no,max(damage_amt) from participated) p on c.reg_no=p.reg_no;

该查询选择的是Car表中的Maximum值和First值，而不是Maximum值对应的值。

Expected Output: Audi A4

Output got:Fiat Punto

Answer 1

我根据给定的数据测试了一个查询，结果是 Audi A4，请执行此查询

SELECT car.model FROM car join participated on car.reg_no=participated.reg_no WHERE  damage_amt=(SELECT max(damage_amt) FROM participated);