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筛选!
表有名字,成绩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筛选!