34,591
社区成员
发帖
与我相关
我的任务
分享
create table 表a
(studentid int,name varchar(10))
insert into 表a
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
create table 表b
(id int,studentid int,subject varchar(10),score int)
insert into 表b
select 1,1,'语文',90 union all
select 2,1,'数学',100 union all
select 3,1,'英语',95 union all
select 4,2,'语文',100 union all
select 5,2,'数学',100 union all
select 6,2,'英语',100 union all
select 6,3,'语文',89 union all
select 7,3,'数学',95 union all
select 8,3,'英语',100
select a.*
from 表a a
inner join
(select studentid
from 表b
group by studentid
having sum(score)=count(1)*100) b on a.studentid=b.studentid
/*
studentid name
----------- ----------
2 李四
(1 row(s) affected)
*/
select * from 表a where studentid not in
(select studentid from 表b score<>100)