日期:2014-05-17 浏览次数:20514 次
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,3
select 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)