mysql分组排序

建表

create table tb_studscore_qlp(user_id varchar(10) charset utf8,type varchar(20)  charset utf8,score int);

insert into tb_studscore_qlp
select 'a' user_id,'语文' as type,95 as score 
union all
select 'a' user_id,'数学' as type,96 as score 
union all
select 'a' user_id,'英语' as type,86 as score 
union all
select 'a' user_id,'化学' as type,87 as score 
union all
select 'b' user_id,'语文' as type,85 as score 
union all
select 'b' user_id,'数学' as type,56 as score 
union all
select 'b' user_id,'英语' as type,75 as score 
union all
select 'b' user_id,'化学' as type,55 as score 
union all
select 'c' user_id,'数学' as type,68 as score 
union all
select 'c' user_id,'政治' as type,78 as score 
union all 
select 'c' user_id,'英语' as type,90 as score

查出每个用户的分数最高的三门课程

1、关联子查询

select user_id, type, score 
from 
(select t1.*, (select count(*) from tb_studscore_qlp t2 where t2.user_id = t1.user_id and t2.score >= t1.score ) rn 
from tb_studscore_qlp t1 
order by user_id, rn ) t 
where rn <= 3;

结果如下：

a 数学 96
a 语文 95
a 化学 87
b 语文 85
b 英语 75
b 数学 56
c 英语 90
c 政治 78
c 数学 68

2、自连接

select
    t1.* from
    tb_studscore_qlp t1,
    tb_studscore_qlp t2
where
    t1.user_id = t2.user_id
and t2.score >= t1.score
group by
    t1.user_id,
    t1.type
having
    count(t2.score) <= 3
order by user_id,score desc

结果如下：

a 数学 96
a 语文 95
a 化学 87
b 语文 85
b 英语 75
b 数学 56
c 英语 90
c 政治 78

c 数学 68

3、子查询

select * from tb_studscore_qlp a 
where exists 
(select count(1) from tb_studscore_qlp b where b.user_id = a.user_id and b.score > a.score having count(1) < 3 )
order by user_id,score desc

结果如下

a 数学 96
a 语文 95
a 化学 87
b 语文 85
b 英语 75
b 数学 56
c 英语 90
c 政治 78
c 数学 68