学生信息userInfo表结构:
学生信息userInfo表数据:
方法1:
select * from userInfo a
where (select count(*)from userInfo b where b.major = a.major and b.age>=a.age )<=2 order by major, age desc;
查询结果:
方法2:
select * from userInfo a where a.id in
(select top 2 id from userInfo b where b.major = a.major order by age desc) order by major, age desc
查询结果: