日期:2014-05-17  浏览次数:20514 次

SQL数据库查询
我现在有三张表学生Student(sID,sName)","课程Course(cID,cName)","选课StudentCourse(scID,sID,cID)" 。一个学生可以选修0..n门课,一门课也可以被0..n个学生选修。
一条SQL语句找出选修了所有课程的学生姓名???
这条SQL语句怎么写?

------解决方案--------------------
group by 分组后,用having来判断
------解决方案--------------------
SQL code

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)