现有三个表学生表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语句怎么写
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语句怎么写
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)
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
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='英语'
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'
where exists(select * from learn where sid=a.sid and cid='C01')
and exists(select * from learn where sid=a.sid and cid='C02')
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=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>
是错的
FROM Student,Course, Learn
WHERE Cid = 'C01'
OR Cid = 'C02'