select s.* from student s left join studentCourse sc on s.sID=sc.sID where cID in(select cID from Course )这个样写效率有可能不高
creat view view1 as select StudentCourse.scID,StudentCourse.cID,StudentCourse.sID,Course.cName from StudentCourse join Course on StudentCourse.cID =Course.cID select view1.*, Student.sName from view1 join Student on view1.sID=Student.sID
create table Student(sID int identity(1,1),sName varchar(20)) insert into Student select '张三' union all select '李四' union all select '王麻子' create table Course(cID int identity(1,1),cName varchar(20)) insert into Course select '语文' union all select '数学' union all select '外语'create table StudentCourse (scID int identity(1,1),sID int ,cID int) insert into StudentCourse select 1,1 union all select 2,1 union all select 3,1 union all select 3,2 union all select 3,3select s.sName from StudentCourse sc inner join Student s on sc.sID = s.sID group by s.sName having count(sc.sID) >= (select count(cID) from Course)
谢谢 我有更好答案了 select sName from Student where sID in ( select sID from StudentCourse group by sID having count(*) =( select COUNT(*) from Course))
select s.*
from student s left join studentCourse sc on s.sID=sc.sID
where cID in(select cID from Course )这个样写效率有可能不高
creat view view1
as
select StudentCourse.scID,StudentCourse.cID,StudentCourse.sID,Course.cName from StudentCourse join Course on StudentCourse.cID =Course.cID
select view1.*, Student.sName from view1 join Student on view1.sID=Student.sID
insert into Student
select '张三' union all
select '李四' union all
select '王麻子' create table Course(cID int identity(1,1),cName varchar(20))
insert into Course
select '语文' union all
select '数学' union all
select '外语'create table StudentCourse (scID int identity(1,1),sID int ,cID int)
insert into StudentCourse
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 3,2 union all
select 3,3select s.sName from StudentCourse sc inner join Student s on sc.sID = s.sID
group by s.sName
having count(sc.sID) >= (select count(cID) from Course)
where sID in (
select sID from StudentCourse group by sID
having count(*) =(
select COUNT(*) from Course))