全日制身份和兼职学生

Question

有一个查询和测试案例，显示全日制和兼职学生的数量。一名全日制学生至少参加了4门课程。一名兼职学生至少参加了一门课程，但不超过3个课程。

尽管查询似乎有效，但似乎有点冗长。我想知道是否有更简洁的方法可以重写查询。此外，我想以每行符合标准的每一行展示学生，

也许会以类似的方式显示？

     , LISTAGG(
         NVL2(s.student_id, s.last_name || ', ' || s.first_name, NULL),
         '; '
       ) WITHIN GROUP (ORDER BY s.last_name, s.first_name) AS students

以下是我的表，数据和查询，如果可能的话，我想缩短。感谢所有回答和您的专业知识的人。

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 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 enrolled_student_course_counts AS (
  SELECT
    s.student_id
    , s.first_name
    , s.last_name 
    , COUNT(sc.course_id) AS course_count
  FROM students s
    LEFT JOIN student_courses sc
    ON s.student_id = sc.student_id
  GROUP BY
    s.student_id
    , s.first_name
    , s.last_name 
  HAVING COUNT(sc.course_id) > 0
)
, student_enrollment_statuses AS (
  SELECT
    student_id
    , first_name 
    , last_name 
    , CASE WHEN course_count >= 4 THEN 'full-time'
           WHEN course_count BETWEEN 1 AND 3 THEN 'part-time'
      END AS student_enrollment_status
  FROM enrolled_student_course_counts
)
SELECT
  UPPER(student_enrollment_status) AS student_enrollment_status
  , COUNT(student_enrollment_status) AS student_enrollment_status_count
FROM student_enrollment_statuses
GROUP BY student_enrollment_status;

Answer 1

由于您只需要 numbers （而不是任何其他数据），请缩短查询，以便它仅搜索student_courses表：表：

SQL> with temp as
  2    (select student_id,
  3            count(course_id) cnt
  4     from student_courses
  5     group by student_id
  6    )
  7  select
  8    sum(case when cnt <  4 then 1 else 0 end) part_time,
  9    sum(case when cnt >= 4 then 1 else 0 end) full_time
 10  from temp;

 PART_TIME  FULL_TIME
---------- ----------
         6          2

SQL>