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

[Jarodsong]

现有三个表学生表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语句怎么写

[zhou__zhou]

DECLARE @学生表 TABLE(Sid VARCHAR(10) , NAME VARCHAR(10))
INSERT INTO @学生表
SELECT 'S01','Jarod' UNION ALL
SELECT 'S02','Mike' UNION ALL
SELECT 'S03','Terry'

DECLARE @选课表 TABLE(Sid VARCHAR(10), Cid VARCHAR(10))
INSERT INTO @选课表
SELECT 'S01', 'C01' UNION ALL
SELECT 'S01', 'C02' UNION ALL
SELECT 'S01', 'C03' UNION ALL
SELECT 'S02', 'C01' UNION ALL
SELECT 'S03', 'C04'

select name from @学生表 where sid IN (select sid from @选课表 WHERE Cid IN('c01','c02')GROUP BY sid HAVING COUNT(*)=2)

[Limpire]

--静态
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

--动态
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

[Limpire]

--原始数据：@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
*/

[火星求索]

select Student.* from 
Student left join Learn on Student.Sid =Learn.Sid
        left join  Course on Learn.Cid =Course. Cid 
where Course.Name='高等数学' and Course.Name='英语'

[pt1314917]

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'

[cxmcxm]

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')

[cxmcxm]

 
select name from 学生表 a 
where not exists(select * from learn where sid=a.sid and cid not in ('C01','C02')
                   and (cid='C01' or  cid='C02'))

[cxmcxm]

对不起
<code=sql>
select name from 学生表 a
where not exists(select * from learn where sid=a.sid and cid not in ('C01','C02')
and (cid='C01' or cid='C02'))

</code>
是错的

[yin_banxian]

SELECT Student.Name
FROM Student,Course, Learn
WHERE Cid = 'C01'
OR Cid = 'C02'

[wipe_tear]

MS-SQL 版的高手就是多

[thornfield_he]

select Distinct Name from Student where sid in(select sid from Learn where Cid=c01 or Cid=c02)