使用左连接查询指定字段值最高的记录

1、查找test表中wealth最大的记录的name和wealth

select s1.name,s1.wealth from test s1  left join test s2 on s1.wealth<s2.wealth where s2.name is null;

2.按照name分组，查找相同name中wealth最大的记录

select s1.name,s1.wealth  from test s1  left join test s2 on s1.name=s2.name and  s1.wealth<s2.wealth where s2.name is null;