10-2 查询各专业学生的平均成绩 分数 10 作者 张庆 单位 集美大学 本题目要求编写SQL语句, 统计各专业的学生选课的平均成绩,如果某专业尚未有任何学生选修课程或成绩为空时,平均分计为0。输出结果集按照major表中的mno升序排序。 表结构: 请在这里写定义表结构的SQL语句。例如: CREATE TABLE `major` ( `mno` char(2) NOT NULL, `mname` varchar(20) NOT NULL, PRIMARY KEY (`mno`) ); CREATE TABLE `stu` ( `sno` char(4) NOT NULL, -- 学号 `sname` char(8) NOT NULL, -- 姓名 `sex` tinyint(1) DEFAULT NULL, -- 性别 `mno` char(2) DEFAULT NULL, -- 专业号 `birdate` datetime DEFAULT NULL, -- 出生日期 `memo` text, -- 备注 PRIMARY KEY (`sno`), CONSTRAINT `fk_stu_mno` FOREIGN KEY (`mno`) REFERENCES `major` (`mno`) ); CREATE TABLE `sc` ( `sno` char(4) NOT NULL, `cno` char(4) NOT NULL, `grade` decimal(6,1) DEFAULT NULL, PRIMARY KEY (`sno`,`cno`), CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`) ); 表样例 请在这里给出上述表结构对应的表样例。例如 major表: major.JPG stu表: stu.JPG sc表: sc.JPG 输出样例: 请在这里给出输出样例。例如: l29B.JPG
时间: 2025-06-05 22:41:58 浏览: 82
### SQL 实现按专业计算学生的平均成绩
为了实现按专业计算学生的平均成绩,并处理未选修课程或成绩为空的情况,可以采用以下方法:
#### 解决方案分析
1. **数据准备**
假设存在两个主要表:`student` 和 `score`。其中:
- 表 `student` 包含字段 `(s_id, s_name, major)`,分别表示学生ID、学生姓名和所属专业。
- 表 `score` 包含字段 `(s_id, c_id, grade)`,分别表示学生ID、课程ID和对应的成绩。
2. **逻辑设计**
需要通过连接这两个表来获取每个专业的学生成绩情况。对于没有选课记录或者成绩为空的学生,应将其视为成绩为0进行计算。最终的结果需按照专业编号升序排列。
3. **SQL 查询构建**
使用 `LEFT JOIN` 将 `student` 表与 `score` 表关联起来,确保即使某专业没有任何选课记录也能显示其平均成绩为0。接着利用聚合函数 `AVG()` 计算每门课程的平均分,并用条件判断解决空值问题[^1]。
以下是完整的 SQL 语句实现:
```sql
SELECT
s.major AS major,
COALESCE(AVG(sc.grade), 0) AS avg_grade
FROM
student s
LEFT JOIN
score sc ON s.s_id = sc.s_id
GROUP BY
s.major
ORDER BY
s.major ASC;
```
#### 关键点解释
- **COALESCE 函数**: 当某个专业的所有学生成绩都为空时,`AVG()` 返回 NULL。因此使用 `COALESCE` 来替代这些 NULL 值为默认值 0[^2]。
- **LEFT JOIN**: 确保即便某些专业没有任何对应的选课记录,在结果集中仍然保留该专业并赋予平均分为0。
- **GROUP BY 和 ORDER BY**: 对于每一个不同的专业 (`major`) 进行分组操作;最后依据专业名称顺序展示结果[^3]。
此查询满足了题目中的需求——即当不存在任何有效成绩时返回零作为平均分数,并且整个列表依照指定的专业编码从小到大排序。
---
###
阅读全文
相关推荐


















