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

最新推荐文章于 2025-03-15 23:33:43 发布

wanhf11

最新推荐文章于 2025-03-15 23:33:43 发布

阅读量1.7w

点赞数

分类专栏： Mysql

本文链接：https://blog.youkuaiyun.com/qq_17612199/article/details/52759453

版权

Mysql 专栏收录该内容

45 篇文章

订阅专栏

本文介绍如何使用SQL从score表中查询学生各科成绩及其总分，并展示如何通过UNION操作合并不同查询结果，包括去重（UNION）和包含所有记录（UNION ALL）的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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
)