34,588
社区成员
发帖
与我相关
我的任务
分享
insert Student
select '张三', '语文',81
union all
select'李四','数学', 90
union all
select '王五', '语文',70
union all
select '王五','数学',100
union all
select '王五','英语', 90
select Sname
from Student
group by Sname
having avg(Mark)>80
create table #1(name varchar(20),subject varchar(10),score int)
insert into #1 select '张三','语文',81
insert into #1 select '李四','数学',90
insert into #1 select '王五','语文',81
insert into #1 select '王五','数学',100
insert into #1 select '王五','英语',90
select * from #1 a
where score>=80
and exists(select 1 from #1 b
where a.name=b.name
group by b.name
having count(1)=(select count(distinct subject) from #1 c ))
and subject in (select distinct subject from #1)
name subject score
-------------------- ---------- -----------
王五 语文 81
王五 数学 100
王五 英语 90
(3 行受影响)
select 姓名 from tb group by 姓名 having min(分数)>80;
create table tb (姓名 varchar(10),科目 varchar(10),分数 int check (分数 between 0 and 100) )
insert tb
select '张三', '语文',81
union all
select'李四','数学', 90
union all
select '王五', '语文',70
union all
select '王五','数学',100
union all
select '王五','英语', 90
select * from tb t1 join (select 姓名,min(分数) mf from tb group by 姓名 )b on b.姓名=t1.姓名 where mf>=80
李四
数学 90 李四 90 张三
语文 81 张三 81
select 姓名
from tableA
group by 姓名
having avg(分数)>80
select name from tb a where not exists(select 1 from tb where score<80 and name=a.name)
select name from tb group by name having count(*)=sum(case when score>=80 then 1 else 0 end)
select name from tb group by name having min(score)>=80
...