sql有两个表，如何计算每个学生的单科成绩、平均成绩和总成绩

根据id进行关联查询

select * from student where name='？'； 获得id 和名字 select * from score where id='？'； 得到所有的成绩，然后计算。。。 然后输出。。。 如果要存 insert into 表名 values(id,成绩,成绩，成绩)； 最后一行你在程序中计算了在存入语句同上。

先内连接查询，再计算平均值，然后再union一下

补充cxxbro的答案 select s1.id as id,s1.score as math,s2.score as english,s1.score+s2.score as sum from student as stu join score as s1 join score as s2 where stu.id=s1.id AND s1.id=s2.id AND s1.subject='math' AND s2.subject='english' union select 'avg' as id,avg(s1.score) as math,avg(s2.score) as english,avg(s1.score+s2.score) from score as s1 join score as s2

( SELECT stuid as id, CAST(sum(case when `subject`='math' then score end) AS SIGNED) as math, CAST(sum(case when `subject`='english' then score end) AS SIGNED) as english, CAST(( sum(case when `subject`='math' then score end) + sum(case when `subject`='english' then score end) ) AS SIGNED) as 'sum' FROM score GROUP BY stuid ) UNION ( SELECT 'avg' as id, CAST(avg(case when `subject`='math' then score end) AS SIGNED) as math, CAST(avg(case when `subject`='english' then score end) AS SIGNED) as english, CAST(( avg(case when `subject`='math' then score end) + avg(case when `subject`='english' then score end) ) AS SIGNED) as 'sum' FROM score )