SQL分类排名，取前N条记录

SQL分类排名，取前N条记录

表有名字，成绩2个字段

----按成绩排名，按人名，选择成绩最高的2条记录
select name,result,count(*) from (
select A.name,B.result from table1 A,table1 B where A.name = B.name and A.result <= B.result
) group by A.name,B.result having count(result) <= 2  ORDER BY NAME,RESULT DESC

----按成绩排名，按人名，选择成绩最低的2条记录
select name,result,count(*) from (
select A.name,B.result from table1 A,table1 B where A.name = B.name and A.result >= B.result
) group by A.name,B.result having count(result) <= 2  ORDER BY NAME,RESULT DESC

核心思路，是通过自关联，使其出现重复记录，然后再通过分组，求count进行having筛选！