多表连接后取分组中某个值为最大的一行

@Select("select DISTINCT b.*, a.sum " +
        "from (SELECT * FROM wage WHERE wid IN  " +
        "(SELECT MAX(wid) FROM wage GROUP BY sid)) a " +
        "left join (select * from staff) b on a.sid = b.sid")

先(SELECT MAX(wid) FROM wage GROUP BY sid)  将薪水表根据外键 员工id来分组 并且查出每个组中最大的值；

SELECT * FROM wage WHERE wid IN " + "(SELECT MAX(wid) FROM wage GROUP BY sid) 查出的结果后给wid进行比较 选择当前行

后面的就是join多表查询了

参考链接：

https://www.dazhuanlan.com/2019/12/23/5e002da115bb0/
https://www.jianshu.com/p/694fb2c75ecd