在学生选课表和学生表中,统计学生平均分在 60~70 和 90~100 的学生成绩记录,包 括学生学号、学生姓名、总分、平均分、最高分、最低分、所修课程门数字段。 实验代码
时间: 2023-12-16 08:54:08 浏览: 31
假设我们有两张表,一张是学生表 Student,包含学生的学号和姓名;另一张是选课表 Course,包含学生选课的记录,每条记录包括学生学号、课程名称、课程成绩。
首先,我们需要联合两张表进行查询,并计算每个学生的总分和平均分:
```
SELECT s.student_id, s.student_name, COUNT(c.course_name) AS num_courses, SUM(c.course_score) AS total_score, AVG(c.course_score) AS avg_score, MAX(c.course_score) AS max_score, MIN(c.course_score) AS min_score
FROM Student s
INNER JOIN Course c ON s.student_id = c.student_id
GROUP BY s.student_id, s.student_name
```
然后,我们需要筛选出平均分在 60~70 和 90~100 之间的学生记录:
```
SELECT *
FROM (
SELECT s.student_id, s.student_name, COUNT(c.course_name) AS num_courses, SUM(c.course_score) AS total_score, AVG(c.course_score) AS avg_score, MAX(c.course_score) AS max_score, MIN(c.course_score) AS min_score
FROM Student s
INNER JOIN Course c ON s.student_id = c.student_id
GROUP BY s.student_id, s.student_name
) AS temp
WHERE avg_score BETWEEN 60 AND 70 OR avg_score BETWEEN 90 AND 100
```
最后,我们可以将结果按照平均分从高到低排序:
```
SELECT *
FROM (
SELECT s.student_id, s.student_name, COUNT(c.course_name) AS num_courses, SUM(c.course_score) AS total_score, AVG(c.course_score) AS avg_score, MAX(c.course_score) AS max_score, MIN(c.course_score) AS min_score
FROM Student s
INNER JOIN Course c ON s.student_id = c.student_id
GROUP BY s.student_id, s.student_name
) AS temp
WHERE avg_score BETWEEN 60 AND 70 OR avg_score BETWEEN 90 AND 100
ORDER BY avg_score DESC
```
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)