oracle PL/SQL：如何在select语句中使用2个列表时消除重复值

jrcvhitl 于 2023-04-20 发布在 Oracle

关注(0)|答案(2)|浏览(134)

上下文：这是一个任务，我必须使用SQL developer重新创建Netflix电影数据库的基本版本。作为该任务的一部分，我必须创建一个视图，让我可以查看电影的标题，出版年份，持续时间，描述，导演列表和演员列表。
SQL开发人员版本：20.4.0.379.2205-x64
PL/SQL版本：11.2.0.2.0 -生产
表格：
NETFLIX_FILMS（film_id，titre，annee_sortie，duration，description）
NETFLIX_ACTEURS（acteur_id，surnom_acteur，prenom_acteur）
NETFLIX_ACTEURS_REF（acteur_id，film_id）
NETFLIX_DIRECTEURS（directeur_id，surnom_directeur，prenom_directeur）
NETFLIX_DIRECTEURS_REF（directeur_id，film_id）
为了帮助解释我遇到的问题，我将使用以下电影示例：
书名：《以利之书》
董事：艾伯特·休斯，艾伦·休斯
演员：Denzel华盛顿，加里Oldman，米拉Kunis
Année sortie：2010
持续时间：118
说明：决心保护一个神圣的文本，承诺保存人类，伊莱继续向西整个贫瘠，后启示录县。
我一直在尝试使用listagg来获取单个字符串的导演列表和单个字符串的演员列表。然而，当我尝试在同一个select语句中使用两个listagg时，它会创建重复的列表，而且我似乎不知道如何删除它们。
下面是我使用的代码：

SELECT
    NETFLIX_FILMS.TITRE,
    NETFLIX_FILMS.ANNEE_SORTIE,
    NETFLIX_FILMS.DURATION,
    NETFLIX_FILMS.DESCRIPTION,
    LISTAGG(NETFLIX_DIRECTEURS.PRENOM_DIRECTEUR 
            || ' ' || NETFLIX_DIRECTEURS.SURNOM_DIRECTEUR || ', ') 
            WITHIN GROUP (ORDER BY PRENOM_DIRECTEUR, SURNOM_DIRECTEUR) 
            AS NOM_DIRECTEUR,
    LISTAGG(NETFLIX_ACTEURS.PRENOM_ACTEUR 
            || ' ' || NETFLIX_ACTEURS.SURNOM_ACTEUR || ', ') 
            WITHIN GROUP (ORDER BY PRENOM_ACTEUR, SURNOM_ACTEUR) 
            AS NOM_ACTEUR
FROM NETFLIX_FILMS
INNER JOIN NETFLIX_ACTEURS_REF
    ON NETFLIX_FILMS.FILM_ID = NETFLIX_ACTEURS_REF.FILM_ID
LEFT OUTER JOIN NETFLIX_ACTEURS
    ON NETFLIX_ACTEURS_REF.ACTEUR_ID = NETFLIX_ACTEURS.ACTEUR_ID
INNER JOIN NETFLIX_DIRECTEURS_REF
    ON NETFLIX_FILMS.FILM_ID = NETFLIX_DIRECTEURS_REF.FILM_ID
LEFT OUTER JOIN NETFLIX_DIRECTEURS
    ON NETFLIX_DIRECTEURS_REF.DIRECTEUR_ID = NETFLIX_DIRECTEURS.DIRECTEUR_ID
GROUP BY NETFLIX_FILMS.TITRE, NETFLIX_FILMS.ANNEE_SORTIE, NETFLIX_FILMS.DURATION, NETFLIX_FILMS.DESCRIPTION;

这给了我以下结果：
书名：《以利之书》
董事：阿尔伯特·休斯，阿尔伯特·休斯，阿尔伯特·休斯，艾伦·休斯，艾伦·休斯，艾伦·休斯
演员：丹泽尔·华盛顿，丹泽尔·华盛顿，加里·奥德曼，加里·奥德曼，米拉库尼斯，米拉·库尼斯
Année sortie：2010
持续时间：118描述：决心保护一个神圣的文本，承诺保存人类，伊莱继续向西整个贫瘠，后启示录县。
预期结果是：
书名：《以利之书》
董事：艾伯特·休斯，艾伦·休斯
演员：Denzel华盛顿，加里Oldman，米拉Kunis
Année sortie：2010
持续时间：118
说明：决心保护一个神圣的文本，承诺保存人类，伊莱继续向西整个贫瘠，后启示录县。
每个actor的directors都是重复的，而actor也是重复的。只有当我在同一个select语句中使用两个listagg时才会发生这种情况。这个版本的SQL不支持Listagg（distinct...），我似乎找不到其他方法来解决这个问题。

oracle

来源：https://stackoverflow.com/questions/76010211/pl-sql-how-to-get-rid-of-duplicate-values-when-using-2-listagg-in-select-statem

2条答案

按热度按时间

qcbq4gxm1#

问题是你有3个演员和2个导演，当你把所有的表连接在一起时，你会得到3x2 = 6行（因为每个演员都与每个导演相连），然后当你聚合时，你会得到所有6行的名字，这给了你重复的名字。你要做的是聚合演员而不加入导演，聚合导演而不加入演员，这样在两个聚合不会生成重复项，也不需要过滤掉它们。您可以通过以下几种方法来实现：
1.使用相关子查询：

SELECT f.TITRE,
       f.ANNEE_SORTIE,
       f.DURATION,
       f.DESCRIPTION,
       ( SELECT LISTAGG(
                  PRENOM_ACTEUR || ' ' || SURNOM_ACTEUR,
                  ', '
                ) WITHIN GROUP (ORDER BY PRENOM_ACTEUR, SURNOM_ACTEUR) 
         FROM   NETFLIX_ACTEURS_REF r
                INNER JOIN NETFLIX_ACTEURS a
                ON r.ACTEUR_ID = a.ACTEUR_ID
         WHERE  f.FILM_ID = r.FILM_ID
       ) AS NOM_ACTEUR,
       ( SELECT LISTAGG(
                  d.PRENOM_DIRECTEUR || ' ' || d.SURNOM_DIRECTEUR,
                  ', '
                ) WITHIN GROUP (ORDER BY PRENOM_DIRECTEUR, SURNOM_DIRECTEUR) 
         FROM   NETFLIX_DIRECTEURS_REF r
                INNER JOIN NETFLIX_DIRECTEURS d
                ON e.DIRECTEUR_ID = d.DIRECTEUR_ID
         WHERE  f.FILM_ID = r.FILM_ID
       ) AS NOM_DIRECTEUR
FROM   NETFLIX_FILMS f;

1.或者聚合然后加入：

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

赞(0）回复(0）举报 2023-04-20

0yycz8jy2#

由于我不确定你的Oracle版本，因此不确定你是否可以在你的版本中使用DISTINCT和LISTAGG（DISTINCT需要19c及以上）。你可以使用这里描述的正则表达式答案来删除重复的内容，因为无论Oracle版本如何，这都应该有效：
Need unique values using with Listagg function
因此，在您的情况下，以下内容适用：

REGEXP_REPLACE(
LISTAGG(NETFLIX_DIRECTEURS.PRENOM_DIRECTEUR|| ' ' || NETFLIX_DIRECTEURS.SURNOM_DIRECTEUR || ', ') WITHIN GROUP (ORDER BY PRENOM_DIRECTEUR, SURNOM_DIRECTEUR),
        '([^,]+)(,\1)+',
        '\1'
) AS NOM_DIRECTEUR,
REGEXP_REPLACE(
LISTAGG(NETFLIX_ACTEURS.PRENOM_ACTEUR|| ' ' || NETFLIX_ACTEURS.SURNOM_ACTEUR || ', ') 
WITHIN GROUP (ORDER BY PRENOM_ACTEUR, SURNOM_ACTEUR),
        '([^,]+)(,\1)+',
        '\1'
) AS NOM_ACTEUR

希望这能帮上忙，祝你好运。

赞(0）回复(0）举报 2023-04-20

我来回答

oracle PL/SQL：如何在select语句中使用2个列表时消除重复值

2条答案

相关问题

热门标签

最新问答