有个表 SC(S#,C#,G) 其中 S#,C#,G分别表示学号、课程编号 和 成绩
统计同时选修’C1’号课程和’C2’号课程的所有学生中，
C1 成绩比C2得分高的学生学号及相应两门的考试成绩
作业题，鉴定完毕
SQL code:
create table #sc(s# varchar(10),c# varchar(10),G int)
insert into #sc
select '001','C1',100 union all
select '001','C2',70 union all
select '002','C1',60 union all
select '002','C2',70 union all
select '003','C1',90 union all
select '003','C2',80 union all
select '004','C2',83
select *from #sc where s# in(
select a.s# from #sc a join #sc b on a.s#=b.s#
and a.G>b.G AND a.c#='C1')
SQL code:
/*=============================================*/
--> author:Ken Wong
--> Add date:2009-12-05 12:04:42
/*=============================================*/
--> 测试数据：@table
declare @table table([S#] varchar(2),[C#] varchar(2),[G] numeric(4,1))
insert @table
select '01','c1',90.5 union all
select '01','c2',100 union all
select '01','c3',80 union all
select '02','c1',90 union all
select '02','c2',80.5 union all
select '02','c3',75 union all
select '03','c1',80 union all
select '04','c3',76
select *
from @table t
where S# in(select S# from @table
where C# in ('C
