在学生选课表和学生表中，统计学生平均分在 60~70 和 90~100 的学生成绩记录，包 括学生学号、学生姓名、总分、平均分、最高分、最低分、所修课程门数字段。 实验代码

假设我们有两张表，一张是学生表 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