老师教的学生最多

Question

我试图写一个查询，显示哪个老师教的学生最多，但我似乎无法克服语法错误。

当我只在CTE中运行代码时，它似乎工作得很好。

下面是我的测试用例和问题查询。有人能告诉我我需要怎么做才能解决这个问题吗？此外，如果有更好的方式编写查询，我将欢迎任何建议。对函数排序的分区分句是否是一个更干净的解决方案？

下面是我的测试用例和查询。预先感谢所有回答的人

CREATE TABLE teachers(teacher_id, first_name,  last_name) AS
   SELECT 101, 'Keith', 'Stein'  FROM dual UNION ALL
  SELECT 102,  'Roger',  'Wood' FROM dual UNION ALL
  SELECT 103,  'Douglas',  'Kern'   FROM dual UNION ALL
  SELECT 104, 'Paul',  'Weber'    FROM dual UNION ALL
  SELECT 105,  'Jeffrey',  'Lebowitz'    FROM dual UNION  ALL
  SELECT 106,  'Gabby',  'Orr'    FROM dual;


CREATE TABLE students(student_id, first_name,  last_name) AS
   SELECT 1, 'Faith', 'Aaron'  FROM dual UNION ALL
  SELECT 2,  'Lisa',  'Saladino' FROM dual UNION ALL
  SELECT 3,  'Leslee',  'Altman'   FROM dual UNION ALL
  SELECT 4, 'Patty',  'Kern'    FROM dual UNION ALL
  SELECT 5,  'Beth',  'Cooper'    FROM dual UNION  ALL
  SELECT 95,  'Zak',  'Despart'    FROM dual UNION  ALL
  SELECT 96,  'Owen',  'Balbert'    FROM dual UNION  ALL
   SELECT 97,  'Jack',  'Aprile'    FROM dual UNION  ALL
  SELECT 98,  'Nicole',  'Kramer'    FROM dual UNION  ALL
   SELECT 99,  'Jill',  'Coralnick'    FROM dual;


CREATE TABLE courses(course_id, course_name, teacher_id, semester) AS
SELECT 1, 'Geometry', 101, '2022-2' FROM DUAL UNION ALL
SELECT 2, 'Trigonometry', 102, '2022-2' FROM DUAL UNION ALL
SELECT 3, 'Calculus', 103, '2022-2' FROM DUAL UNION ALL
SELECT 4, 'Chemistry', 104, '2022-2' FROM DUAL UNION ALL 
SELECT 5, 'Biology', 105, '2022-2' FROM DUAL UNION ALL 
SELECT 6, 'Physcology', 106, '2022-2' FROM DUAL; 

CREATE TABLE student_courses (student_id,course_id) AS
SELECT 1, 1 FROM dual UNION ALL
SELECT 2, 1 FROM dual UNION ALL
SELECT 3, 1 FROM dual UNION ALL
SELECT 4, 1 FROM dual UNION ALL
SELECT 5, 1 FROM dual UNION ALL
SELECT 1, 2 FROM dual UNION ALL
SELECT 2, 2 FROM dual UNION ALL
SELECT 3, 2 FROM dual UNION ALL
SELECT 4, 2 FROM dual UNION ALL
SELECT 5, 2 FROM dual UNION ALL
SELECT 1, 3 FROM dual UNION ALL
SELECT 2, 3 FROM dual UNION ALL
SELECT 3, 3 FROM dual UNION ALL
SELECT 4, 3 FROM dual UNION ALL
SELECT 5, 3 FROM dual UNION ALL
SELECT 97, 1 FROM dual UNION ALL 
SELECT 97, 3 FROM dual UNION ALL 
SELECT 97, 5 FROM dual UNION ALL
SELECT 97, 6 FROM dual UNION ALL
SELECT 98, 3 FROM dual UNION ALL 
SELECT 98, 4 FROM dual UNION ALL
SELECT 98, 5 FROM dual UNION ALL
SELECT 99, 2 FROM dual UNION ALL 
SELECT 99, 4 FROM dual UNION ALL
SELECT 99, 5 FROM dual UNION ALL
SELECT 99, 6 FROM dual;


WITH teacher_student_rankings AS (
  SELECT
    t.teacher_id
    , t.first_name
    , t.last_name 
    , COUNT(DISTINCT sc.student_id) AS teacher_student_count
    , RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC) AS teacher_student_rank
  FROM teachers t
    LEFT JOIN courses c
    ON t.teacher_id = c.teacher_id
    LEFT JOIN student_courses sc
    ON c.course_id = sc.course_id
  GROUP BY
    t.teacher_id
    , t.first_name
    , t.last_name 
)
SELECT
  teacher_id
  , first_name
  , last_name 
FROM teacher_student_rankings
WHERE teacher_student_rank = 1:

Answer 1

请找出下面的答案

        WITH teacher_student_rankings AS (
      SELECT
        t.teacher_id
        , t.first_name
        , t.last_name 
        , COUNT(DISTINCT sc.student_id) AS teacher_student_count
        , RANK() OVER (ORDER BY COUNT(DISTINCT sc.student_id) DESC) AS teacher_student_rank
      FROM teachers t
        LEFT JOIN courses c
        ON t.teacher_id = c.teacher_id
        LEFT JOIN student_courses sc
        ON c.course_id = sc.course_id
      GROUP BY
        t.teacher_id
        , t.first_name
        , t.last_name 
    )
    SELECT
     t.teacher_id
        , t.first_name
        , t.last_name
        ,t.teacher_student_count as "No of students per teacher"
        ,t.teacher_student_rank
    FROM teacher_student_rankings t
    where teacher_student_rank =1;