欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL 分组计算 topN

程序员文章站 2024-02-14 09:50:10
...

在线运行SQL

首先安利这款免费在线 SQL 运行平台 sql fiddle
SQL 分组计算 topN

建表:

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;

SQL 分组计算 topN

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; 

SQL 分组计算 topN
我们看到,虽然找的是 top3,但是并列第三也找到了,这是上面两种方法无法做到的