写法比较多,不一一写了。可能手误 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 ...
楼上的兄弟,select 1 from tb, 这1在这句里是什么意思?
select 姓名 from tableA group by 姓名 having avg(分数)>80
select name from dt group by name having min(score)>80 select name from dt where name in(select name from dt where score>80 and name=dt.name) group by name
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 '王五','英语', 90select * from tb t1 join (select 姓名,min(分数) mf from tb group by 姓名 )b on b.姓名=t1.姓名 where mf>=80 李四 数学 90 李四 90 张三 语文 81 张三 81
select 姓名 from tb group by 姓名 having min(分数)>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 '王五','英语',90select * 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 行受影响)
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
select name from tb group by name having min(score)>80 and count(subject)=3这样是说3门功课都有成绩且均在80分以上的人
select 1 from tb 输出的结果显示:把所有结果行的值至为1;即结果有多少行,显示多少个1 1 1 1 ... 测试结果表明: 如果表中有重复数据, 这个的结果集中有重复select name from tb a where not exists(select 1 from tb where score<80 and name=a.name)用group by 分组是正确的,避免重复数据select name from tb group by name having min(score)>=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
...
from tableA
group by 姓名
having avg(分数)>80
select name from dt where name in(select name from dt where score>80 and name=dt.name) group by name
select '张三', '语文',81
union all
select'李四','数学', 90
union all
select '王五', '语文',70
union all
select '王五','数学',100
union all
select '王五','英语', 90select * from tb t1 join (select 姓名,min(分数) mf from tb group by 姓名 )b on b.姓名=t1.姓名 where mf>=80
李四
数学 90 李四 90 张三
语文 81 张三 81
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 '王五','英语',90select * 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 行受影响)
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
输出的结果显示:把所有结果行的值至为1;即结果有多少行,显示多少个1 1
1
1
...
测试结果表明:
如果表中有重复数据,
这个的结果集中有重复select name from tb a where not exists(select 1 from tb where score<80 and name=a.name)用group by 分组是正确的,避免重复数据select name from tb group by name having min(score)>=80