【每个分组里面都取】
Oracle
【关键dense_rank() over(partition by s_class order by s_score desc)】rn<=3
s_class 分组字段，s_score 排序字段
select s_class,s_no,s_name,rn
from
(select s_class,s_no,s_name,dense_rank() over(partition by s_class order by s_score desc)as rn
from
(select a.s_no,a.s_name,a.s_class,b.s_score
from selecttest.student a
left join
(select s_no,sum(sc_degree)as s_score
from selecttest.score
group by s_no)b
on a.s_no=b.s_no)c)d
where rn<=3
mysql
关键【(select count(*)+1 from student where s_class=t1.s_class and s_no>t1.s_no) as group_id】a.group_id<=2
内外表交叉统计
select a.*
from
(
select t1.*,(select count(*)+1 from student where s_class=t1.s_class and s_no>t1.s_no) as group_id
from student t1
) a
where a.group_id<=2
###########################################################################
mysql详细（来源多表）
select a.*
from
(
select t1.s_class,t1.total_score,(select count(*)+1 from (
SELECT
s_class,
SUM(sc_degree) AS total_score
FROM
student
JOIN score ON student.s_no = score.s_no
GROUP BY
s_class,
s_name
) s where s.s_class=t1.s_class and s.total_score>t1.total_score) as group_id
from (
SELECT
s_class,
SUM(sc_degree) AS total_score
FROM
student
JOIN score ON student.s_no = score.s_no
GROUP BY
s_class,
s_name
) t1
) a
where a.group_id<=1
【数据库版本高的话可以这样写】
WITH temp_table AS (
SELECT s_class, SUM(sc_degree) AS total_score
FROM student
JOIN score ON student.s_no = score.s_no
GROUP BY s_class, s_name
)
SELECT a.*
FROM (
SELECT t1.s_class, t1.total_score, (
SELECT COUNT(*) + 1
FROM temp_table s
WHERE s.s_class = t1.s_class AND s.total_score > t1.total_score
) AS group_id
FROM temp_table t1
) a
WHERE a.group_id <= 1;
【附加，取前三个最高的分组（取总分最高的前三个班级）】
SELECT
s.s_class,
count(*) as count
FROM
student s
WHERE
1 = 1
GROUP by
s_class
order by
count desc
limit 2