PostgreSQL中如何给STRING_AGG添加条件筛选分数低于60的记录

我有如下PostgreSQL查询（来自Codewars的Kata）：

SELECT * FROM (
  SELECT 
    s.id AS student_id,
    MIN(s.name) AS name,
    CASE 
      WHEN COUNT(c.course_name) = 0 THEN 'quit studying' 
      WHEN SUM(CAST(c.score < 60 AS INTEGER)) >=3
        THEN CONCAT('failed in ', STRING_AGG(CONCAT(c.course_name, '(', c.score, ')'), ', ' ORDER BY c.course_name)) 
      ELSE NULL
    END AS reason
  FROM students s
  FULL OUTER JOIN courses c
    ON c.student_id = s.id
  GROUP BY s.id
) df
WHERE reason IS NOT NULL
ORDER BY student_id

该查询目前可正常运行，但需要微调：使用STRING_AGG合并多行生成结果时，仅需包含score低于60的记录，当前结果与期望结果存在差异。

调整后的查询 #

SELECT * FROM (
  SELECT 
    s.id AS student_id,
    MIN(s.name) AS name,
    CASE 
      WHEN COUNT(c.course_name) = 0 THEN 'quit studying' 
      WHEN SUM(CAST(c.score < 60 AS INTEGER)) >=3
        THEN CONCAT('failed in ', STRING_AGG(
          -- 仅保留不及格的课程记录
          CASE WHEN c.score < 60 THEN CONCAT(c.course_name, '(', c.score, ')') END,
          ', ' ORDER BY c.course_name
        )) 
      ELSE NULL
    END AS reason
  FROM students s
  FULL OUTER JOIN courses c
    ON c.student_id = s.id
  GROUP BY s.id
) df
WHERE reason IS NOT NULL
ORDER BY student_id

调整说明 #

• 在STRING_AGG函数内部新增CASE判断，仅当课程成绩低于60分时才拼接课程名称和成绩的字符串，否则返回NULL
• STRING_AGG默认会忽略NULL值，因此最终合并后的结果只会包含不及格的课程信息，符合需求

内容的提问来源于stack exchange，提问作者Amir saleem