SQL 求班级在年级中的排名

[lawsong]

只涉及高一和高二。 高二分文理科目。但这些都很问题关联不大。

表结构如下：
成绩表：
RID 成绩编号
SID 学生编号
TID 考试编号
Class 班级编号
Subject 科目编号
Score 分数

班级表：
CID 班级编号
Name 班级名称
Grade 年级名称


我现在的句子是这样的：

SELECT
AVG(A.[Score]) AS 平均分,
(SELECT (SELECT COUNT(1) FROM
(SELECT DISTINCT [SCORE] FROM [RESULTS] WHERE [TID] = 2 AND [Class] IN (SELECT [Name] FROM [Classes]
WHERE [Grade] = (SELECT [Grade] FROM [Classes] WHERE [Name] = '高二一班'))) T1
WHERE T1.[SCORE]>= [SCORE])) AS 级排名
FROM [Results] AS A WHERE [Class] = '高二一班' AND [TID] = 2

得出的结果不正确。因为我数据库中高二就两个班级。 而这条语句就显示排名是 6.

[bancxc]

按什么排啊 一个班的总成绩？

[feixianxxx]

太多的子查询....
楼主给出数据和你要的结果。。。

[lawsong]

按一个班的平均成绩。不分科目。

[luoyoumou]

[Quote=引用 3 楼 lawsong 的回复:]
按一个班的平均成绩。不分科目。
[/Quote]

---两张靠什么字段关联？

[让你望见影子的墙]

with
wang as (
       select 年级名称 ,班级名称 ,均分=avg(分数)
       from 成绩表 s join 班级表 t on s.班级编号=t.班级编号
         group by 年级名称 ,班级名称
         )

select 排名=row_number over( partition by 年级名称 order by 均分 desc ),*
from wang

未测试

[lawsong]

[Quote=引用 4 楼 luoyoumou 的回复:]
引用 3 楼 lawsong 的回复:
按一个班的平均成绩。不分科目。


---两张靠什么字段关联？
[/Quote]

只有两个年级啊。 所以就没做年级表了。

[lawsong]

5楼的貌似不行啊。我用的是 SqlServer 2005

每个 GROUP BY 表达式必须至少包含一个不是外部引用的列。

[lawsong]

来个人啊。。。 救命啊。

[dawugui]

[Quote=引用楼主 lawsong 的回复:]
只涉及高一和高二。  高二分文理科目。但这些都很问题关联不大。

表结构如下：
成绩表：
RID  成绩编号
SID  学生编号
TID  考试编号
Class 班级编号
Subject  科目编号
Score    分数

班级表：
CID  班级编号
Name 班级名称
Grade 年级名称


我现在的句子是这样的：

SELECT
AVG(A.[Score]) AS 平均分,
(SELECT (SELECT COUNT(1) FROM
(SELECT DISTINCT [SCORE] FROM [RESULTS] WHERE [TID] = 2 AND [Class] IN (SELECT [Name] FROM [Classes]
WHERE [Grade] = (SELECT [Grade] FROM [Classes] WHERE [Name] = '高二一班'))) T1
WHERE T1.[SCORE]>= [SCORE])) AS 级排名
FROM [Results] AS A WHERE [Class] = '高二一班' AND [TID] = 2

得出的结果不正确。因为我数据库中高二就两个班级。  而这条语句就显示排名是  6.


[/Quote]

select t1.* , px = (select count(1) from 
(
  select avg(m.分数) avg_score , m.TID , n.Grade , n.Name from 成绩表 m , 班级表 n where m.Class = n.CID group by m.TID , n.Grade , n.Name
) t2 where TID = t1.TID and Grade = t1.Grade and Name = t1.Name and avg_score > t1.avg_score) + 1 from
(
  select avg(m.分数) avg_score , m.TID , n.Grade , n.Name from 成绩表 m , 班级表 n where m.Class = n.CID group by m.TID , n.Grade , n.Name
) t1

[ChinaJiaBing]

select 平均分=avg(a.score),排名=(select count(1) from results where a.class=class 
              and score>=a.score)
    from results a
where class='高二一班' and tid=2

[xcaicaix]

SELECT b.班级名称,AVG（SELECT Score FORM 成绩表 group by 班级
SLECT
FORM 成绩表 c，班级表 b
WHERE c.班级编号 = b.班级编号
ORDER BY AVG（SELECT Score FORM 成绩表 group by b.班级编号）


对了，提醒楼主，你的表设计的很不合理，感觉最多是第二范式

[xcaicaix]

SELECT b.班级名称,AVG（SELECT Score FORM 成绩表 group by 班级
FORM 成绩表 c，班级表 b
WHERE c.班级编号 = b.班级编号
ORDER BY AVG（SELECT Score FORM 成绩表 group by b.班级编号）


对了，提醒楼主，你的表设计的很不合理，感觉最多是第二范式

[lawsong]

谢谢楼上各位啊。本以为就这么沉了。 晚上一看。又这么对回复。感动。

先谢谢了！

[lawsong]

@xcaicaix
你好，你认为怎么设计比较好?
其实这表我也是设计了几次。自己也感觉不怎么好。
但是，一般做这样系统的。普遍是这么设计的。
烦请赐教。谢谢。

[lawsong]

@dawugui
你好，我试了下你的语句。发现现在怎么都是排名第一了。。。

@xcaicaix
你好，你的句子算出的都是 15，3，49之类的结果。。。

@ChinaJiaBing
你好，你的结果同上。 而且还是一个班的数据有几条。应该是一个班的排名数据只有一条的。