找出所有科目成绩都大于某一学科平均成绩的学生
数据准备:
create table score1(
stuid varchar(20),
couid varchar(20),
score1 int
);
insert into score1 values
('1001','01',100)
,('1001','02',100)
,('1001','03',100)
,('1002','01',90)
,('1002','02',70)
,('1002','03',50)
,('1003','01',80)
,('1003','02',60)
,('1003','03',40);
sql实现:
这里通过关联实现
select t1.stuid
,t1.couid
,t1.score1
from score1 t1
left join(
select couid
,avg(score1) avg_score
from score1
group by 1
) t2
on t1.couid = t2.couid
where t1.score1 >t2.avg_score
实现效果: