SQL 分组计算 topN

在线运行SQL

首先安利这款免费在线 SQL 运行平台 sql fiddle：

建表：

create table score 
( 
	name varchar(20), 
	subject varchar(20), 
	score int 
);

-- 2.插入测试数据 
insert into score(name,subject,score) values('张三','语文',98);
insert into score(name,subject,score) values('张三','数学',80);
insert into score(name,subject,score) values('张三','英语',90);
insert into score(name,subject,score) values('李四','语文',88);
insert into score(name,subject,score) values('李四','数学',86);
insert into score(name,subject,score) values('李四','英语',88);
insert into score(name,subject,score) values('李明','语文',60);
insert into score(name,subject,score) values('李明','数学',86);
insert into score(name,subject,score) values('李明','英语',88);
insert into score(name,subject,score) values('林风','语文',74);
insert into score(name,subject,score) values('林风','数学',99);
insert into score(name,subject,score) values('林风','英语',59);
insert into score(name,subject,score) values('严明','英语',96);

分组 topN

row_number()

-- 语法形式：　　　　ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 
-- 解释：   　　　　根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
-- 常用的使用场景： 取每个学科的前3名
-- 适用于 oracle、postgreSQL、HQL，好像不适用于 mysql、sqlserver

select subject, name, score from 
( 
  select subject,name,score,
    ROW_NUMBER() over (PARTITION by subject order by score desc) as num 
  from score 
) T 
where T.num <= 3 order by subject;

union all

写起来比较蛋疼

(select * from score where subject='语文' order by score desc limit 3)
union all
(select * from score where subject='数学' order by score desc limit 3)
union all
(select * from score where subject='英语' order by score desc limit 3);

自关联

解释一下：从表中找到这样的分数，使得同一学科中比它高的少于 3

select
  t1.name,
  t1.subject,
  t1.score
from
  score t1 left join score t2
on
  t1.subject = t2.subject and
  t1.score < t2.score
group by
  t1.name, t1.subject
having
  count(t2.score) < 3
order by
  t1.subject,t1.score desc;

或者

SELECT * 
FROM score a
WHERE 
( 
  SELECT COUNT(*) 
  FROM score b 
  WHERE a.subject = b.subject 
  AND a.score < b.score 
) <3
ORDER BY a.subject, a.score DESC; 

我们看到，虽然找的是 top3，但是并列第三也找到了，这是上面两种方法无法做到的