有两张表 学生跟班级学生中有评分这个字段
班级中也有评分这个字段我需要把 学生中评分最高的数取出来 放到 班级表中的 评分字段中select max(avgscore) as max , classid from xuesheng group by classid然后用这些 数据 分别 update class set avgscore = max where classid = classid这点我知道.. 求高手帮我一条 sql 语句就搞定...求助
班级中也有评分这个字段我需要把 学生中评分最高的数取出来 放到 班级表中的 评分字段中select max(avgscore) as max , classid from xuesheng group by classid然后用这些 数据 分别 update class set avgscore = max where classid = classid这点我知道.. 求高手帮我一条 sql 语句就搞定...求助
where A.classid = classid
) ORupdate class A INNER JOIN (select max(avgscore) as max , classid from xuesheng group by classid) B
ON A.classid = B.classid set A.avgscore=B.MAX
set a.avgscore=b.max
where a.classid=b.classid
类似这样。
sid int not null auto_increment primary key,
score int,
class_id int
);
create table csdn_class(
class_id int not null auto_increment primary key,
score int
);
insert into csdn_student(score,class_id)
select 10,1 union all
select 8,1 union all
select 9,1 union all
select 9,2 union all
select 8,2 union all
select 8,2 union all
select 7,2 union all
select 7,3 union all
select 4,3;insert into csdn_class(score)
values(0),(0),(0),(0),(0),(0),(0);update
csdn_class a inner join (
select class_id,max(score) as max_score
from csdn_student
group by class_id) b on a.class_id=b.class_id
set a.score=b.max_score