使用listagg將所有學生分組為教師、課程、學期

我的關系是老師->課程->注冊->學生。我正在嘗試使用 listagg 將所有學生的名字/姓氏與teacher_id 和 course_id 放在同一行。另外，我想添加每個teacher_id，course_id，學期的計數。

下面是我的測驗案例，其中包含表格、資料和部分查詢。我將不勝感激完成查詢的任何幫助。提前感謝所有回答的人。

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,  'Carol',  'Seltzer'    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 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 enrollment(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 99, 3 FROM dual;


/* list all teachers, courses, student count, all students for teacher_id,  course_id, semester
*/

SELECT
    t.teacher_id 
  , t.first_name
  , t.last_name 
  , c.course_id
  , c.course_name 
  , c.semester
FROM teachers t
  LEFT JOIN courses c
  ON t.teacher_id  = c.teacher_id 
ORDER BY  teacher_id;

TEACHER_ID    FIRST_NAME    LAST_NAME    COURSE_ID    COURSE_NAME    SEMESTER
101    Keith    Stein    1    Geometry    2022-2
102    Roger    Wood    2    Trigonometry    2022-2
103    Douglas    Kern    3    Calculus    2022-2
104    Paul    Weber    4    Chemistry    2022-2
105    Jeffrey    Lebowitz    5    Biology    2022-2
106    Carol    Seltzer    6    Physcology     2022-2

uj5u.com熱心網友回復：

您可以使用相關的子查詢：

SELECT t.teacher_id 
     , t.first_name
     , t.last_name 
     , c.course_id
     , c.course_name 
     , c.semester
     , (
         SELECT LISTAGG(s.last_name || ', ' || s.first_name, '; ')
                  WITHIN GROUP (ORDER BY s.last_name, s.first_name)
         FROM   enrollment e
                INNER JOIN students s
                ON (e.student_id = s.student_id)
         WHERE  e.course_id = c.course_id
       ) AS students
FROM   teachers t
       LEFT JOIN courses c
       ON t.teacher_id  = c.teacher_id 
ORDER BY
       teacher_id;

其中，對于樣本資料，輸出：

TEACHER_ID	名	姓	COURSE_ID	課程名	學期	學生
101	基思	斯坦因	1	幾何學	2022-2	亞倫，信仰；奧特曼，萊斯利；庫珀，貝絲；克恩，帕蒂；薩拉迪諾，麗莎
102	羅杰	木頭	2	三角學	2022-2	亞倫，信仰；奧特曼，萊斯利；庫珀，貝絲；克恩，帕蒂；薩拉迪諾，麗莎
103	道格拉斯	克恩	3	結石	2022-2	亞倫，信仰；奧特曼，萊斯利；庫珀，貝絲；科拉尼克，吉爾；克恩，帕蒂；薩拉迪諾，麗莎
104	保羅	韋伯	4	化學	2022-2	無效的
105	杰弗里	勒博維茨	5	生物學	2022-2	無效的
106	頌歌	蘇打水	6	生理學	2022-2	無效的

或者您可以使用JOINs 和聚合：

SELECT t.teacher_id 
     , MAX(t.first_name) AS first_name
     , MAX(t.last_name) AS last_name
     , c.course_id
     , MAX(c.course_name) AS course_name
     , MAX(c.semester) AS semester
     , LISTAGG(
         NVL2(s.student_id, s.last_name || ', ' || s.first_name, NULL),
         '; '
       ) WITHIN GROUP (ORDER BY s.last_name, s.first_name) AS students
     , COUNT(s.student_id) AS num_students
FROM   teachers t
       LEFT OUTER JOIN courses c
       ON t.teacher_id  = c.teacher_id
       LEFT OUTER JOIN (
         enrollment e
         INNER JOIN students s
         ON (e.student_id = s.student_id)
       )
       ON (e.course_id = c.course_id)
GROUP BY
       t.teacher_id,
       c.course_id
ORDER BY
       t.teacher_id,
       c.course_id;

db<>在這里擺弄

標籤：甲骨文 数数 列表格

上一篇：如何僅計算VARCHAR陣列中的值？

下一篇：從sql中檢索JSON