班级均分在年级排序用一句SLQ语句如何写

Question

我有一个“学生成绩”表，其表的结构大致如下:
ID 班级 姓名 语文 数学 英语 总分
1 1 张三 95 90 90 275
2 8 李四 95 95 90 280
3 5 王五 95 90 90 282
4 1 周二 90 90 90 270
5 1 郑六 85 85 90 260把以上表信息在网页上以原序显示出:
ID 班级 姓名 语文 数学 英语 总分 班级排名
1 1 张三 90 90 90 270 2
2 8 李四 95 95 90 280
3 5 王五 95 90 90 282
4 1 张三 90 95 95 280 1
5 1 张三 85 85 90 260 3每个同学的总分在班级的排序情况，使用下面SQL语句用asp语句<%=rs("班级名次")%>输出成功：
sql="select(select count(*)from 学生成绩 where 总分>=a.总分 and 年级="&grade&" and 班级="&class&") as 班级名次,* from 学生成绩 a where 年级="&grade&" and 班级="&class&" order by 总分 desc"２.  但还想在网页上设一子表，报告班级情况，想显示“总分”列的各班平均分的排名，如下所示：
班级　总分平均分　名次
以 1 到 8 班的顺序列表显示其中年级，班级都是变量，想在网页面上循环输出：班级　总分平均分　名次
可以建立一新表，以avg函数中求出了均分后排序，但这样太麻烦。
在不建新表的情况下，班级均分在年级排序用一句SLQ语句如何写？

Answer 1

SELECT 
*,ROW_NUMBER() OVER (ORDER BY [总分平均分] DESC)
FROM (
SELECT [班级],AVG(总分) AS [总分平均分] FROM [学生成绩]
) T
ORDER BY [总分平均分] DESC

Answer 2

谢谢大师，但我的数据库是ACCESS,您这句好像只能在MSSQL 2005以上运行，如果是在ACCESS中，用ASP调用，该如何写呀？

Answer 3

SELECT 
*,(
SELECT COUNT(1) FROM (
(
SELECT [班级],AVG(总分) AS [总分平均分] FROM [学生成绩]
) T2 WHERE T2.[总分平均分]>=T1.[总分平均分]
)
)
FROM (
SELECT [班级],AVG(总分) AS [总分平均分] FROM [学生成绩]
) T1
ORDER BY [总分平均分] DESC

Answer 4

select * from(
select *,
(select count(*) from (select 班级,sum(总分) as 总分数,avg(总分) as 平均分 from 学生成绩表 group by 班级) a where 平均分>=t1.平均分) as 排名
from
(
 select 班级,sum(总分) as 总分数,avg(总分) as 平均分 from 学生成绩表 group by 班级
)t1
) order by 排名
/*
排名 班级 总分数 平均分
1 5 282 282
2 8 280 280
3 1 805 268.333333333333
*/

Answer 5

特别感谢大家的鼓励和帮助!!!!!!本人实验了6楼的回复,大致操作如下：
1. 先用 max 函数求出有多少班级
2. 再用 for i =1 to MAX(值) 循环输出下面内容：
    班级    均分　排名
　　1.
        2.
        3.
        ...
        8. （我试验的班级有8个）
　因为要求班级顺序不变，所以班级是从1到8，班级，均分显示没错，排名这个语句我是用 <%=rs("排名")%>,但排序下全是1,没有2,3...8。这个SQL语句嵌套太长,还是没完全消化哟。“排名”能用<%=rs("排名")%>循环输出对吗?还望大师们指导指导！