17,377
社区成员
发帖
与我相关
我的任务
分享
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