select * from student where stuID in(
select stuID from grade
where grade>90
group by stuid having count(*)=(select count(*) from cource))
select stuID from grade
where grade>90
group by stuid having count(*)=(select count(*) from cource))
where grade>90 group by stuid having count(*)=(select count(*) from cource))
stuID chinesename
1 allen
go
insert into #student select 1,'allen',1,500 union all
select 2,'tom',1,0 union all
select 3,'jordan',1,1000 union all
select 4,'kobe',1,0
go
create table #grade(graID int,stuID int,courceID int,grade decimal(4,1))
insert into #grade select 1,4,1,80.8 union all
select 2,4,2,90 union all
select 3,4,3,100 union all
select 4,3,1,55 union all
select 5,3,2,86 union all
select 6,3,3,90.5 union all
select 7,1,1,95 union all
select 8,1,2,99.5 union all
select 9,1,3,96 union all
select 10,2,1,60 union all
select 11,2,2,66 union all
select 12,2,3,68
go
create table #cource(courceID int,courceName nvarchar(20),xuefen int)
insert into #cource select 1,'语文',5 union all
select 2,'数学',6 union all
select 3,'外语',6
select gg.stuID,S.chinesename from #student S join (select g.stuID from #grade g left join #cource c on c.courceID=g.courceID where g.grade>90 group by g.stuID having count(*)=(select count(*) from #cource)) gg on
gg.stuID=S.stuID
where stuID in
(
select stuID from (select count (1) cnt, StuID from grade where grade>=90 group by StuID) a
where cnt=(select COUNT(1) from cource)
)
/*
stuID chinesename sex scholarship
----------- -------------------- ----------- ---------------------
1 allen 1 500.0000(所影响的行数为 1 行)
*/
where g.grade>=90 group by s.stuID,s.chinesename having count(g.courceID)=(select count(*) from cource)
select * from student where stuid not in (select distinct stuid from grade where grade < 90)