sql 查询科目成绩以及平均成绩

来源：互联网发布：linux 重启后挂载失败编辑：程序博客网时间：2024/04/20 07:11

score表
stuid subject score
1 math 80
1 english 90
2 math 81
2 english 91
3 math 85
3 english 95

要求得到的组合查询结果
id math english sum
1 80 90 170
2 81 91 172
3 85 95 180
avg 82 92 174

(    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)

union 对两个结果集进行并集操作，重复数据只显示一次
union All，对两个结果集进行并集操作，重复数据全部显示

0 0