Select studentNumber,studentName,(English+JSP+CPP+Java+Access) as 总分, '排名'=Identity(int,1,1) Into #temptable From studentScore order by 总分 desc
Select * From #temptable order by 总分 desc
Drop Table #temptable
上面这条语句是我写的，但是结果排名这一列不正确。
下面是网上的两种方法实现了对成绩排名但是我看不懂。具体网址如下：
http://blog.163.com/bxf_0011/blog/static/35420330200932410841927/
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;