一,一张表student(stuid,stuname,subject,score)
第一种方式:row_number () over(partition by 列名1 order by 列名2)
使用的navicat中没有row_number()over语法。未实现。
第二种方式:先取一个新表a,左连接至原表,对score栏位做比较,条件为a.subject = b.subject and a.score<b.score,其实就是列出同一门课内所有分数比较的情况。出现两次及以下,说明在这个科目中处于前2排名
select a.* from student as a
left join student as b
on a.`subject` = b.`subject` and a.score<b.score
group by a.`subject`, a.stuid
having count(b.`subject`)<2
order by a.`subject`
第三种方式:与第二种方式类似,where和having用法的区别
SELECT a.* FROM student as a WHERE
(
SELECT COUNT(1) FROM student as b WHERE
a.`subject`=b.`subject` AND b.score>=a.score
)<=2
ORDER BY a.`subject`,a.score DESC