如何在LISTAGG函数中处理空值，替换为'no actors'并消除多余点

解决单个演员拼接的多余点问题 #

当first_name为空时，直接拼接. 会产生多余的点，用CASE语句可以精准控制每个演员的显示格式：

SELECT 
  length, 
  title, 
  LISTAGG(
    CASE 
      WHEN first_name IS NOT NULL THEN SUBSTR(first_name, 1, 1) || '. ' || last_name
      ELSE last_name
    END, 
    ', '
  ) WITHIN GROUP (ORDER BY last_name) AS ACTORS
FROM film 
INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id)
WHERE release_year = 1991
GROUP BY title, length
ORDER BY length DESC;

逻辑说明：

• 当first_name不为空时，正常显示「首字母. 姓氏」格式
• 当first_name为空时，仅显示姓氏，避免多余的点

当存在空first_name的演员时，整体显示'no actors' #

如果需求是只要某部电影有演员的first_name为空，就将ACTORS列统一显示为'no actors'，可以在分组后用CASE结合COUNT判断是否存在空值：

SELECT 
  length, 
  title, 
  CASE 
    WHEN COUNT(CASE WHEN first_name IS NULL THEN 1 END) > 0 THEN 'no actors'
    ELSE LISTAGG(SUBSTR(first_name, 1, 1) || '. ' || last_name, ', ') WITHIN GROUP (ORDER BY last_name)
  END AS ACTORS
FROM film 
INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id)
WHERE release_year = 1991
GROUP BY title, length
ORDER BY length DESC;

包含无演员的电影并显示'no actors' #

原查询用INNER JOIN会过滤掉没有演员的电影，如果需要包含这类电影并显示'no actors'，需要改成LEFT JOIN，再用NVL处理空的聚合结果：

SELECT 
  f.length, 
  f.title, 
  NVL(
    LISTAGG(
      CASE 
        WHEN a.first_name IS NOT NULL THEN SUBSTR(a.first_name, 1, 1) || '. ' || a.last_name
        ELSE a.last_name
      END, 
      ', '
    ) WITHIN GROUP (ORDER BY a.last_name), 
    'no actors'
  ) AS ACTORS
FROM film f
LEFT JOIN film_actor fa USING (film_id)
LEFT JOIN actor a USING (actor_id)
WHERE f.release_year = 1991
GROUP BY f.title, f.length
ORDER BY f.length DESC;

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