一条SQL语句查询出成绩名次 排名 (转)

 堂弟的同学找我，他要写一个学生成绩管理系统，其中一个模块需要提供用户查询学生成绩名次排名，名次排名里需要考虑到成绩相同的情况。这个让我想起N年前我读书时候做这个问题，当时我用了最笨的方法，使用程序来排名，而没有使用SQL语句，自然效率相当低下。

需求简述：

例如我有这样一个成绩表 编号 姓名 成绩 
1 张三 90 
2 李四 85 
3 王五 100 
4 赵六 85 
5 汪一 100 
6 周九 78 
7 何二 56 


然后我希望排名次，因为这里有同分，所以必然会出现并列名次。网络上关于并列成绩名次排名有两种方式，其一是这样：

A
名次 姓名 成绩 
1 王五 100 
1 汪一 100 
3 张三 90 
4 赵六 85 
4 李四 85 
6 周九 78 
7 何二 56 


第二种还是这样：

B
名次 姓名 成绩 
1 王五 100 
1 汪一 100 
2 张三 90 
3 赵六 85 
3 李四 85 
4 周九 78 
5 何二 56 


SQL语句是一种很实用的技巧，希望和朋友们多交流，现在抛砖引玉把自己的3句SQL语句贴出来共享，抛砖引玉，希望大家多多指教，拍砖的轻点。

对于A方式，比较好办，以Access数据库为例（其他数据库语法大同小异，变化一下对应的即可），可以写成这样：
SELECT e.place AS 名次, d.name AS 姓名, d.mark AS 成绩
FROM [select a.id,count(b.id)+1 as place from sc a left join sc b on a.mark < b.mark group by a.id]. AS e INNER JOIN sc AS d ON e.id = d.id;


同时A方式也可以写成这样，效果等同，不过估计比上面那句效率低点：
SELECT e.place AS 名次, d.name AS 姓名, d.mark AS 成绩
FROM [select a.id,count(iif(b.id is null,null,b.id))+1 as place from sc a left join sc b on a.mark<b.mark group by a.id]. AS e INNER JOIN sc AS d ON e.id = d.id;

而B方式则比较棘手，写了半个钟头才写好.......：
SELECT e.place AS 名次, d.name AS 姓名, d.mark AS 成绩
FROM [select c.id,count(iif(c.mark is null,null,c.mark))+1 as place from (select a.id,b.mark from sc a left join sc b on a.mark < b.mark group by a.id,b.mark)c group by c.id]. AS e INNER JOIN sc AS d ON e.id = d.id;
转载处：http://www.i-sx.net/bbs/viewthread.php?tid=157887

转载于:https://www.cnblogs.com/zhangchenliang/archive/2012/12/05/2804189.html