PL/SQL：SELECT语句中使用两个LISTAGG如何去除重复值

问题根源是你同时关联了演员和导演的关联表，导致产生笛卡尔积。比如某电影有2个导演、3个演员，关联后会生成6条记录，最终LISTAGG时每个导演会被重复拼接3次，每个演员重复2次。

因为Oracle 11g不支持LISTAGG(DISTINCT)，可以通过先分别聚合导演和演员列表，再关联主表的方式解决：

SELECT
    f.TITRE,
    f.ANNEE_SORTIE,
    f.DURATION,
    f.DESCRIPTION,
    d.NOM_DIRECTEUR,
    a.NOM_ACTEUR
FROM NETFLIX_FILMS f
-- 关联预聚合的导演列表
LEFT JOIN (
    SELECT
        dr.FILM_ID,
        LISTAGG(d.PRENOM_DIRECTEUR || ' ' || d.SURNOM_DIRECTEUR || ', ') 
            WITHIN GROUP (ORDER BY d.PRENOM_DIRECTEUR, d.SURNOM_DIRECTEUR) AS NOM_DIRECTEUR
    FROM NETFLIX_DIRECTEURS_REF dr
    LEFT JOIN NETFLIX_DIRECTEURS d ON dr.DIRECTEUR_ID = d.DIRECTEUR_ID
    GROUP BY dr.FILM_ID
) d ON f.FILM_ID = d.FILM_ID
-- 关联预聚合的演员列表
LEFT JOIN (
    SELECT
        ar.FILM_ID,
        LISTAGG(a.PRENOM_ACTEUR || ' ' || a.SURNOM_ACTEUR || ', ') 
            WITHIN GROUP (ORDER BY a.PRENOM_ACTEUR, a.SURNOM_ACTEUR) AS NOM_ACTEUR
    FROM NETFLIX_ACTEURS_REF ar
    LEFT JOIN NETFLIX_ACTEURS a ON ar.ACTEUR_ID = a.ACTEUR_ID
    GROUP BY ar.FILM_ID
) a ON f.FILM_ID = a.FILM_ID;

代码说明： #

• 第一个子查询：针对每个电影，单独聚合导演信息，生成无重复的导演字符串列表
• 第二个子查询：同理，单独聚合演员信息，生成无重复的演员字符串列表
• 主查询将电影表与这两个预聚合的子查询关联，避免了笛卡尔积的产生，自然不会出现重复值

如果需要创建视图，直接把整个查询用CREATE VIEW 视图名 AS包裹即可：

CREATE VIEW NETFLIX_FILM_DETAILS AS
SELECT
    f.TITRE,
    f.ANNEE_SORTIE,
    f.DURATION,
    f.DESCRIPTION,
    d.NOM_DIRECTEUR,
    a.NOM_ACTEUR
FROM NETFLIX_FILMS f
LEFT JOIN (
    SELECT
        dr.FILM_ID,
        LISTAGG(d.PRENOM_DIRECTEUR || ' ' || d.SURNOM_DIRECTEUR || ', ') 
            WITHIN GROUP (ORDER BY d.PRENOM_DIRECTEUR, d.SURNOM_DIRECTEUR) AS NOM_DIRECTEUR
    FROM NETFLIX_DIRECTEURS_REF dr
    LEFT JOIN NETFLIX_DIRECTEURS d ON dr.DIRECTEUR_ID = d.DIRECTEUR_ID
    GROUP BY dr.FILM_ID
) d ON f.FILM_ID = d.FILM_ID
LEFT JOIN (
    SELECT
        ar.FILM_ID,
        LISTAGG(a.PRENOM_ACTEUR || ' ' || a.SURNOM_ACTEUR || ', ') 
            WITHIN GROUP (ORDER BY a.PRENOM_ACTEUR, a.SURNOM_ACTEUR) AS NOM_ACTEUR
    FROM NETFLIX_ACTEURS_REF ar
    LEFT JOIN NETFLIX_ACTEURS a ON ar.ACTEUR_ID = a.ACTEUR_ID
    GROUP BY ar.FILM_ID
) a ON f.FILM_ID = a.FILM_ID;

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