如果是查询超过所在系平均成绩的学生姓名和所在系的话,那么当求每个学生平均成绩 select sname,sdept from student,sc where student.sno=sc.sno group by sname having avg(grade) 求每个系平均成绩 select sname,sdept from student,sc where student.sno=sc.sno group by sdept,sname 然后怎么合起来呢?
上面这个回复 意思不大懂 "那么当求每个学生平均成绩" 不是求超过系平均分就可以了么 要学生平均分干嘛?这个平均分还是没定义明确 最好上一点测试数据和想要的结果 不然容易混淆 一般的基础思路 根据要求扩展 具体的要看实际数据了select 姓名,系名 from ... where 分数 > (select avg(分数) from t where t.系名=系名)
用基本的sql做出来 不知道是这个意思不 另外 我说的是上面列举的数据 没代表性 一个系的人员就列了一个 怎么比 肯定都会查出来了 with student as ( select '95001' sno,'李勇' sname,'CS' sdept from dual union all select '95002' sno,'刘晨' sname,'IS' sdept from dual union all select '95003' sno,'王敏' sname,'MA' sdept from dual union all select '95004' sno,'张立' sname,'IS' sdept from dual ),SC as ( select '95001' sno,1 cno,92 grade from dual union all select '95001' sno,2 cno,85 grade from dual union all select '95001' sno,3 cno,88 grade from dual union all select '95002' sno,2 cno,90 grade from dual union all select '95003' sno,3 cno,80 grade from dual )select t1.sname,t1.sdept from student t1, (select sno,avg(grade) sg from sc group by sno) t2, (select sdept,avg(grade) ag from student a,sc b where a.sno = b.sno group by sdept) t3 where t1.sno = t2.sno and t1.sdept = t3.sdept and t2.sg >= t3.ag
select sname,sdept
from student,sc
where student.sno=sc.sno
group by sname
having avg(grade)
求每个系平均成绩
select sname,sdept
from student,sc
where student.sno=sc.sno
group by sdept,sname
然后怎么合起来呢?
一般的基础思路 根据要求扩展 具体的要看实际数据了select 姓名,系名
from ...
where 分数 > (select avg(分数) from t where t.系名=系名)
Sno Sname Ssex Sage Sdept
95001 李勇 男 20 CS
95002 刘晨 女 19 IS
95003 王敏 女 18 MA
95004 张立 男 19 ISCourse表
Cno Cname Cpno Credit
1 数据库 5 4
2 数学 2
3 信息系统 1 4
4 操作系统 6 3
5 数据结构 7 4
6 数据处理 2
7 PASCAL语言 6 4SC表
Sno Cno Grade
95001 1 92
95001 2 85
95001 3 88
95002 2 90
95002 3 80
with student as
(
select '95001' sno,'李勇' sname,'CS' sdept from dual union all
select '95002' sno,'刘晨' sname,'IS' sdept from dual union all
select '95003' sno,'王敏' sname,'MA' sdept from dual union all
select '95004' sno,'张立' sname,'IS' sdept from dual
),SC as
(
select '95001' sno,1 cno,92 grade from dual union all
select '95001' sno,2 cno,85 grade from dual union all
select '95001' sno,3 cno,88 grade from dual union all
select '95002' sno,2 cno,90 grade from dual union all
select '95003' sno,3 cno,80 grade from dual
)select t1.sname,t1.sdept
from student t1,
(select sno,avg(grade) sg
from sc
group by sno) t2,
(select sdept,avg(grade) ag
from student a,sc b
where a.sno = b.sno
group by sdept) t3
where t1.sno = t2.sno and t1.sdept = t3.sdept
and t2.sg >= t3.ag