表Student
sid sname
-----------------
s1 韩婷
s2 杨玉
s3 李阳
s4 焦阳
表Score
Cid Sid Score
------------------
c1 s1 90
c1 s2 86
c1 s3 92
c2 s1 86
c2 s2 56
c3 s3 86
c3 s2 75
c4 s4 89
c4 s2 93----
问题
查询选修过“c1”和“c2”课程的学员并且选修"c2"的成绩比选修"c1"的课程成绩低的学号和姓名;
帮帮忙 在线等 谢谢大家了
sid sname
-----------------
s1 韩婷
s2 杨玉
s3 李阳
s4 焦阳
表Score
Cid Sid Score
------------------
c1 s1 90
c1 s2 86
c1 s3 92
c2 s1 86
c2 s2 56
c3 s3 86
c3 s2 75
c4 s4 89
c4 s2 93----
问题
查询选修过“c1”和“c2”课程的学员并且选修"c2"的成绩比选修"c1"的课程成绩低的学号和姓名;
帮帮忙 在线等 谢谢大家了
if object_id('tempdb.dbo.#Student') is not null drop table #Student
go
create table #Student (sid varchar(2),sname varchar(4))
insert into #Student
select 's1','韩婷' union all
select 's2','杨玉' union all
select 's3','李阳' union all
select 's4','焦阳'
--> 测试数据: #Score
if object_id('tempdb.dbo.#Score') is not null drop table #Score
go
create table #Score (Cid varchar(2),Sid varchar(2),Score int)
insert into #Score
select 'c1','s1',90 union all
select 'c1','s2',86 union all
select 'c1','s3',92 union all
select 'c2','s1',86 union all
select 'c2','s2',56 union all
select 'c3','s3',86 union all
select 'c3','s2',75 union all
select 'c4','s4',89 union all
select 'c4','s2',93select a.sid ,s.sname
from #Score a
join #score b on a.sid=b.sid and a.score>b.score
join #Student s on a.sid=s.sid
where a.cid='c1' and b.cid='c2'
sid sname
---- -----
s1 韩婷
s2 杨玉(2 行受影响)