select a.sid,a.sname
from student a
left join sc b on a.sid=b.sid and b.cid='01'
left join sc c on a.sid=c.sid and c.cid='02'
where isnull(b.score,0)>isnull(c.score,0)

------解决方案--------------------
SQL code

select SId,Sname
    from Student S
    where exists (select 1 from SC S1,SC S2
        where S1.SId=S.SId and S2.SId=S.SId
            and S1.Score>S2.Score 
            and S1.CId='01' and S2.CId='02')

------解决方案--------------------
SQL code

select a.SId,a.Sname
from Student a
inner join
(select SId,Score
from SC where CId='02') b on a.SId=b.SId
inner join
(select SId,Score
from SC where CId='01') c on a.SId=c.SId
where b.Score<c.Score