--原始数据：@Student
declare @Student table(Sid varchar(3),Name varchar(5))
insert @Student
select 'S01','Jarod' union all
select 'S02','Mike' union all
select 'S03','Terry'
--原始数据：@Learn
declare @Learn table(Sid varchar(3),Cid varchar(3))
insert @Learn
select 'S01','C01' union all
select 'S01','C02' union all
select 'S01','C03' union all
select 'S02','C01' union all
select 'S03','C04'

--静态
select a.Name
from @Student a join @Learn b on a.Sid=b.Sid
where b.Cid in ('C01','C02')
group by a.Name
having(count(1))>=2
/*
Name
-----
Jarod
*/

--动态
declare @Cids varchar(100)
set @Cids='C01,C02'
select a.Name
from @Student a join @Learn b on a.Sid=b.Sid
where charindex(','+b.Cid+',',','+@Cids+',')>0
group by a.Name
having(count(1))>=len(@Cids)-len(replace(@Cids,',',''))+1
/*
Name
-----
Jarod
*/

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

declare @t1 table(sid varchar(10), name varchar(20))
insert into @t1 select 'S01',   'Jarod' 
insert into @t1 select 'S02',   'Mike' 
insert into @t1 select 'S03',   'Terry'


declare @t2 table(Cid varchar(10), name varchar(20))
insert into @t2 select 'C01',   '高等数学' 
insert into @t2 select 'C02',   '英语' 
insert into @t2 select 'C03',   '操作系统'
insert into @t2 select 'C04',   '数据结构'

declare @t3 table(sid varchar(10), Cid varchar(20))
insert into @t3 select 'S01','C01'  
insert into @t3 select 'S01','C02'  
insert into @t3 select 'S01','C03'  
insert into @t3 select 'S02','C01'  
insert into @t3 select 'S03','C04' 

select (select name from @t1 where sid=a.sid)  from @t3 a where sid in (select sid from @t3 where cid='c01') and cid='c02'

------解决方案--------------------
select name from 学生表 a  
where exists(select * from learn where sid=a.sid and cid='C01')
    and exists(select * from learn where sid=a.sid and cid='C02')