row_number() and partition

作者: wilsoncai 分类: sql 发布时间: 2018-01-06 01:00

一个表有三列,id,name,score,现在要取得每个人分数最高的两门课程

create temp table tmp_xxx
as
select 1 id,'A' name,80 grade
UNION ALL
select 2,'A',90
UNION ALL
select 3,'A',60
UNION ALL
select 4,'B',100
UNION ALL
select 5,'B',90
UNION ALL
select 6,'B',50;



select id,name,grade
from 
(
select id,name,grade, row_number() over (partition by name order by grade desc) as RN
from
tmp_xxx
) tmp
where RN<=2;

发表评论

电子邮件地址不会被公开。 必填项已用*标注