使用Listagg将所有学生分组为老师，课程，Semster

Question

我有一个老师的关系 - ＆gt;课程 - ＆GT;注册学生。我正在尝试使用ListAgg将所有学生的首先/姓氏与the Cotherton_id和course_id同一行中。此外，我想添加每个Teach_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

Answer 1

您可以使用相关的子查询：

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;

对于示例数据，输出：

cotherton_id	first_name	last_name	course_id	course_name	学期	学生
101	keith	stein	1	几何	2022-2	aaron，信仰；阿尔特曼，莱斯利；库珀，贝丝；克恩，帕蒂； Saladino，丽莎
102	roger	木材	2	trigonometry	2022-2	aaron，信仰；阿尔特曼，莱斯利；库珀，贝丝；克恩，帕蒂； Saladino，丽莎
103	douglas	kern	3	cyculus	2022-2	aaron，信仰；阿尔特曼，莱斯利；库珀，贝丝； Coralnick，吉尔；克恩，帕蒂； Saladino，丽莎
104	paul	weber	4	化学	2022-2	null
105	jeffrey	lebowitz	5	生物学	2022-2	null
106	carol	seltzer	6	Physcology	2022-2	null

---

或者您可以使用 join s和汇总：

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＆lt;＆gt; fiddle “ nofollow noreferrer”>在这里