select * from student where sno in; (select sno from studentcourse group by cno having count(*)=2)
到底是group by cno还是sno啊?
应该是group by sno,因为先要对sno分组,然后统计选课的门数
create table Student(Sno int,Sname nvarchar(50)) create table Course(Cno int,Cname nvarchar(50)) create table StudentCourse(Sno int,Cno int,Grade int)insert into Student select '1','zsl' union select '2','zsl1' union select '3','zsl2'goinsert into Course select '1','maths' union select '2','english' union select '3','chinese' goinsert into StudentCourse select '1','1','50' union select '1','2','50' union select '2','1','80' goselect * from Student a where exists( select * from StudentCourse where sno=a.sno group by sno having count(sno)>1 )
select * from Student a where exists( select * from StudentCourse where sno=a.sno group by sno having count(sno)=2 )
Sorry,前面写错了,应该是group by sno
drop table Student drop table Course drop table StudentCoursecreate table Student(Sno int,Sname nvarchar(50)) create table Course(Cno int,Cname nvarchar(50)) create table StudentCourse(Sno int,Cno int,Grade int)insert into Student select '1','zsl' union select '2','zsl1' union select '3','zsl2'goinsert into Course select '1','maths' union select '2','english' union select '3','chinese' goinsert into StudentCourse select '1','1','50' union select '1','2','50' union select '2','1','80' go select * from student where sno in (select sno from studentcourse group by sno having count(*)>=2)
(select sno from studentcourse group by cno having count(*)=2)
create table Course(Cno int,Cname nvarchar(50))
create table StudentCourse(Sno int,Cno int,Grade int)insert into Student select '1','zsl'
union
select '2','zsl1'
union
select '3','zsl2'goinsert into Course select '1','maths'
union
select '2','english'
union
select '3','chinese'
goinsert into StudentCourse select '1','1','50'
union
select '1','2','50'
union
select '2','1','80'
goselect * from Student a
where exists(
select * from StudentCourse where sno=a.sno group by sno having count(sno)>1
)
where exists(
select * from StudentCourse where sno=a.sno group by sno having count(sno)=2
)
drop table Course
drop table StudentCoursecreate table Student(Sno int,Sname nvarchar(50))
create table Course(Cno int,Cname nvarchar(50))
create table StudentCourse(Sno int,Cno int,Grade int)insert into Student select '1','zsl'
union
select '2','zsl1'
union
select '3','zsl2'goinsert into Course select '1','maths'
union
select '2','english'
union
select '3','chinese'
goinsert into StudentCourse
select '1','1','50'
union
select '1','2','50'
union
select '2','1','80'
go
select * from student where sno in
(select sno from studentcourse group by sno having count(*)>=2)