mysql_取分组后的前几行值

--方法一:
select a.id,a.SName,a.ClsNo,a.Score
    from Table1 a left join Table1 b on a.ClsNo=b.ClsNo and a.Score<b.Score
        group by a.id,a.SName,a.ClsNo,a.Score
        having count(b.id)<2
    order by a.ClsNo,a.Score desc

--方法二:
select *
    from Table1 a
        where 2>(select count(*) from Table1 where ClsNo=a.ClsNo  and Score>a.Score)
    order by a.ClsNo,a.Score desc

--方法三:
select *
    from Table1 a
        where id in (select id from Table1 where ClsNo=a.ClsNo order by Score desc limit 2)
    order by a.ClsNo,a.Score desc

 

posted @ 2017-10-10 15:49  依然是等待  阅读(662)  评论(0编辑  收藏  举报