像下面 我想获取表中 男女中成绩最好的学生
获取按照性别分类的最大值,
select *
from
user u left join user e
on u.gender = e.gender and u.score < e.score
where
e.score is null
这个样子查询出来的是
然后我们想要 男女生分组,想要两条,就加上group by gender
select * from user u left join user e on u.gender = e.gender and u.score < e.score where e.score is null group by gender
我们还想按照从大到小排序。先是100,再是98,那么就再加上 order by score desc
select u.* from user u left join user e on u.gender = e.gender and u.score < e.score where e.score is null group by gender order by score desc
结果就是我们想要的
最后给一个数据库的在线编辑 网站,但当然是 根据他给的表,咱们去做各种场景的测试,写各种场景的SQL语句了