求所有学生的平均成绩及其名次

[theoffspring]

有个sc表，学生成绩表：
名称 类型
--------------------------------------------------------------
SID NUMBER 学生编号
CID NUMBER 课程编号
SCORE NUMBER 成绩

现在我想求所有学生的平均成绩及其名次，写了一个总报那个错，但我看不出怎么错了。请高手帮帮忙。

select s.sid,1+(
    select count(score) from sc where sc.sid!=s.sid group by sc.sid 
    having avg(sc.score)<(select avg(sc.score) from sc where s.sid=sc.sid)
) 名次
from sc s;

要求用标准sql，不能用数据库特有的函数

[sgtzzc]

select 
  a.SID,a.score as avg_score,
  (select count(1)+1 from (select sid,avg(score) as score from sc group by sid) b where a.sid=b.sid and a.score<b.score)
from
  (select sid,avg(score) as score from sc group by sid) a

[theoffspring]

跑不通，报错：

SQL> select
2 a.SID,a.score as avg_score,
3 (select count(1)+1 from (select sid,avg(score) as score from sc group by sid) b where a.sid=b.sid and a.score<b.score)
4 from
5 (select sid,avg(score) as score from sc group by sid) a;
(select sid,avg(score) as score from sc group by sid) a
*
ERROR 位于第 5 行:
ORA-00979: 不是 GROUP BY 表达式

[SQL77]

SELECT
 SID,AVG_score,
(SELECT COUNT(*) FROM (SELECT A.SID,AVG(A.score) AVG_score FROM TB A GROUP BY A.SID)AS T  WHERE T.AVG_score>=T1.AVG_score) AS NUM
FROM 
(SELECT A.SID,AVG(A.score) AVG_score FROM TB A GROUP BY A.SID)AS T1

[华夏小卒]

create table t(SID int, CID int,SCORE int )
insert  t select 
1, 11,70 union all select        
2, 11,60 union all select 
3, 11,50 union all select 
4, 11,80 union all select 
5, 11,90 union all select 
1, 12,60 union all select 
2, 12,60 union all select 
3, 12,60 union all select 
4, 12,60 union all select 
5, 12,60 

select sid,score=avg(score) into #t
from t
group by sid

select sid,score,pm=(select count(*) from #t where score>= t.score)
from #t t

sid         score       pm
----------- ----------- -----------
1           65          3
2           60          4
3           55          5
4           70          2
5           75          1

(5 行受影响)


drop table #t

drop table t

[dawugui]

select t1.SID , t1.SCORE 平均成绩 , t2.px 名次 from
(select SID , avg(SCORE) SCORE from sc group by SID) t1,
(select m.* , px = (select count(1) from (select SID , avg(SCORE) SCORE from sc group by SID) n where SCORE > t.SCORE) + 1 from (select SID , avg(SCORE) SCORE from sc group by SID) m) t2
where t1.sid = t2.sid

[dawugui]

--如果是2005,求名次那步可以使用row_number()

[theoffspring]

[Quote=引用 4 楼 js_szy 的回复:]
SQL codecreatetable t(SIDint, CIDint,SCOREint )insert tselect1,11,70unionallselect2,11,60unionallselect3,11,50unionallselect4,11,80unionallselect5,11,90unionallselect1,12,60unionallselect2,12,60unionallselect3,12,60unionallselect4,12,60unionallselect5,12,60select sid,score=avg(score)into #tfrom tgroupby sidselect sid,score,pm=(selectcount(*)from #twhere score>= t.score)from #t t

sid score pm----------- ----------- -----------16532604355547025751

(5 行受影响)droptable #tdroptable t
[/Quote]
临时表也不能用，纯sql

[theoffspring]

[Quote=引用 3 楼 sql77 的回复:]
SQL codeSELECT
SID,AVG_score,
(SELECTCOUNT(*)FROM (SELECT A.SID,AVG(A.score) AVG_scoreFROM TB AGROUPBY A.SID)AS TWHERE T.AVG_score>=T1.AVG_score)AS NUMFROM
(SELECT A.SID,AVG(A.score) AVG_scoreFROM TB AGROUPBY A.SID)AS T1
[/Quote]
你这个我在oracle 9i里报错：

SQL> SELECT
2 SID,AVG_score,
3 (SELECT COUNT(*) FROM (SELECT A.SID,AVG(A.score) AVG_score FROM sc A GROUP BY A.SID) T WHERE T.AVG_score>=T1.AVG_score) NUM
4 FROM
5 (SELECT A.SID,AVG(A.score) AVG_score FROM sc A GROUP BY A.SID) T1
6 ;
(SELECT A.SID,AVG(A.score) AVG_score FROM sc A GROUP BY A.SID) T1
*
ERROR 位于第 5 行:
ORA-00979: 不是 GROUP BY 表达式

[theoffspring]

[Quote=引用 5 楼 dawugui 的回复:]
SQL codeselect t1.SID , t1.SCORE 平均成绩 , t2.px 名次from
(select SID ,avg(SCORE) SCOREfrom scgroupby SID) t1,
(select m.* , px= (selectcount(1)from (select SID ,avg(SCORE) SCOREfrom scgroupby SID) nwhere SCORE> t.SCORE)+1from (select SID ,avg(SCORE) SCOREfrom scgroupby SID) m) t2where t1.sid= t2.sid
[/Quote]

oracle9i:

SQL> select t1.SID , t1.SCORE 平均成绩 , t2.px 名次 from
2 (select SID , avg(SCORE) SCORE from sc group by SID) t1,
3 (select m.* , px = (select count(1) from (select SID , avg(SCORE) SCORE from sc group by SID) n where SCORE > t.SCORE) + 1 from (select SID , avg(SCORE) SCORE from sc group by SID) m) t2
4 where t1.sid = t2.sid
5 ;
(select m.* , px = (select count(1) from (select SID , avg(SCORE) SCORE from sc group by SID) n where SCORE > t.SCORE) + 1 from (select SID , avg(SCORE) SCORE from sc group by SID) m) t2
*
ERROR 位于第 3 行:
ORA-00923: 未找到预期 FROM 关键字