(高手请进)查询学生成绩超过其选修课程平均成绩的课程号

表S--------------> Sno(学号)         Sn(姓名)        Sd(院系）         Sa(年龄)
表C--------------> Cno(课程号)       Cn(课程名)      Pcno(先修课)
表SC-------------> Sno(学号)         Cno(课程号)     g(成绩）

请问如何实现(用SELECT语句）

查询学生成绩超过其选修课程平均成绩的课程号

14 个解决方案

#1

沙发

#2

..又是学生表

#3

Easy

#4

http://hi.csdn.net/link.php?url=http://blog.csdn.net%2Ffredrickhu

去看吧

#5

以前有人问过，
蹭分

#6

有数据没有``？

#7

蹭``

#8

简单

#9

意思我还没搞懂，请列出点数据这样好看懂？？

#10

第一个学生表基本上没用

#11



select a.cno

from sc a inner join

  (

    select sno,avg(g) as g

    from sc

    group by sno

  ) b

  on a.sno=b.sno

where a.g>b.g

#12

if object_id(N'S') is not null drop table S
create table S(Sno int,Sn nvarchar(20),Sd int,Sa int)
insert into S(Sno,Sn,Sd,Sa)
select 1,'Test1',1,23 union all
select 2,'Test2',1,23 union all
select 3,'Test3',2,21 union all
select 4,'Test4',2,23 union all
select 5,'Test5',2,22 union all
select 6,'Test6',1,22 union all
select 7,'Test7',1,22
if object_id(N'C') is not null drop table C
create table C(Cno int,Cn nvarchar(20),Pcno int)
insert into C(Cno,Cn,Pcno)
select 1,'Cn1',1 union all
select 2,'Cn2',1 union all
select 3,'Cn3',1

if object_id(N'SC') is not null drop table SC
create table SC(Sno int,Cno int,g int)
insert into SC(Sno,Cno,g)
select 1,1,77 union all
select 1,2,87 union all
select 1,3,34 union all
select 2,1,89 union all
select 2,2,43 union all
select 2,3,98 union all
select 3,1,89 union all
select 3,2,76 union all
select 3,3,87 union all
select 4,1,77 union all
select 4,2,87 union all
select 4,3,65 union all
select 5,1,89 union all
select 5,2,54 union all
select 5,3,88 union all
select 6,1,54 union all
select 6,2,43 union all
select 6,3,66 union all
select 7,1,88 union all
select 7,2,98 union all
select 7,3,78
go

select * from SC s left join( select Cno,avg(g) as avgg from SC group by Cno)c
on c.Cno=s.Cno left join S ss on s.Sno=ss.Sno where c.avgg<s.g

#13

select *
FROM SC
INNER JOIN (SELECT Sno, AVG(Grade) Grade FROM SC GROUP BY Sno) A ON SC.Sno = A.Sno
WHERE SC.Grade >= A.Grade

#14

select a.cno
from sc a inner join
  (
    select sno,avg(g) as g
    from sc
    group by sno
  ) b
  on a.sno=b.sno
where a.g>b.g

学习学习啊

#1

沙发

#2