先求班级人数再求专业人数再求学院人数最后求全校人数-mysql

在MySQL中，如果你想要依次计算每个班级的人数，然后每个专业的学生数，接着每个学院的学生数，最终得到全校总人数，可以按照递进的方式编写多层嵌套查询。这里假设有一个`students`表，它包含字段`class_id`, `major_id`, 和 `college_id`：

WITH class_counts AS (
  SELECT class_id, COUNT(student_id) as student_count
  FROM students
  GROUP BY class_id
), 

major_counts AS (
  SELECT major_id, class_id, SUM(student_count) as major_student_count
  FROM class_counts
  GROUP BY major_id, class_id
), 

college_counts AS (
  SELECT college_id, major_id, SUM(major_student_count) as college_student_count
  FROM major_counts
  GROUP BY college_id, major_id
)

SELECT SUM(college_student_count) as total_students_across_all
FROM college_counts;

这个查询首先通过`WITH`子句创建了三个临时表（`class_counts`、`major_counts`和`college_counts`），分别表示班级、专业和学院级别的学生数量。最后，外部查询计算全校总人数。