老有关问题：sql语句表示关系代数里的除法

老问题：sql语句表示关系代数里的除法
现有三个表学生表Student(Sid, Name)，课程表Course(Cid, Name)，选课表Learn(Sid, Cid)

其中学生表数据
Sid Name
----------------
S01 Jarod
S02 Mike
S03 Terry

其中课程表数据
Cid Name
----------------
C01 高等数学
C02 英语
C03 操作系统
C04 数据结构

其中选课表Learn数据
Sid Cid
-----------------
S01 C01  
S01 C02  
S01 C03  
S02 C01  
S03 C04  

求同时选了C01、C02两门课的学生的姓名。SQL语句怎么写


[解决办法]

SQL code

--原始数据：@Studentdeclare @Student table(Sid varchar(3),Name varchar(5))insert @Studentselect 'S01','Jarod' union allselect 'S02','Mike' union allselect 'S03','Terry'--原始数据：@Learndeclare @Learn table(Sid varchar(3),Cid varchar(3))insert @Learnselect 'S01','C01' union allselect 'S01','C02' union allselect 'S01','C03' union allselect 'S02','C01' union allselect 'S03','C04'--静态select a.Namefrom @Student a join @Learn b on a.Sid=b.Sidwhere b.Cid in ('C01','C02')group by a.Namehaving(count(1))>=2/*Name-----Jarod*/--动态declare @Cids varchar(100)set @Cids='C01,C02'select a.Namefrom @Student a join @Learn b on a.Sid=b.Sidwhere charindex(','+b.Cid+',',','+@Cids+',')>0group by a.Namehaving(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')