数据表如下:
需求:取每门课程的前3名
一、mysql:
SELECT
course,
name,
score
FROM
student_score t1
WHERE
(
select
count(*)
from
student_score t2
where
t1.course = t2.course
AND t1.score<t2.score)<3
order by
course,
score desc
说明 :
要取每门课程的Top3,就要对t1表做where条件筛选,筛选条件即为:t1的分数在同一课程联结时,小于t2分数的个数要小于3。
二、clickhouse:
select
course,
name,
score
from
(
select
course,
groupArray(3)(name) as name,
groupArray(3)(score) as score
from
(
select
*
from
student_score
order by
score desc) t1
group by
course) t2
array join name, score
说明:首先确保分数倒序,再使用groupArray函数对每门课程的姓名和分数自上而下取3条进行合并,从而过滤出每门课的前3名,最后使用array join将合并的姓名和分组展开。
结果如下: