select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t where t.rk<=3;
利用聚合函数rank()是一个不错的解决办法。 rank()的语法: RANK ( expr [, expr]... ) WITHIN GROUP ( ORDER BY expr [ DESC | ASC ] [NULLS { FIRST | LAST }] [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]... )
where t.rk<=3;
rank()的语法:
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)