sql - 如何从Oracle中的多个LISTAGG'ed列中消除重复项

我缺少从以下脚本中的listagg函数中删除重复值的逻辑是什么？

sys.privs表如下：

SQL> desc sys.privs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE                                            VARCHAR2(30 CHAR)
 GRANTED_ROLE                              NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)

以下是脚本

SELECT grantee,
     LISTAGG(granted_role, '-') WITHIN GROUP (ORDER BY granted_role) AS granted_role,
     LISTAGG(privilege, '-') WITHIN GROUP (ORDER BY privilege) AS privs,
     owner,
     table_name
FROM (
     SELECT UNIQUE
            grantee,
            granted_role,
            privilege,
            owner,
            table_name
       FROM sys.privs
     )
GROUP BY grantee, owner, table_name;

最佳答案

您可以利用listagg()忽略NULL值这一事实来做到这一点。因此，使用row_number()枚举重复项，然后仅提取第一个值：

SELECT grantee,
       LISTAGG(CASE WHEN seqnum_granted_role = 1 THEN granted_role END, '-')
           WITHIN GROUP (ORDER BY granted_role) AS granted_role,
       LISTAGG(CASE WHEN seqnum_privilege = 1 THEN privilege END, '-')
           WITHIN GROUP (ORDER BY privilege) AS privs,
       owner,
       table_name
FROM (SELECT grantee, granted_role, privilege,
             owner, table_name,
             ROW_NUMBER() OVER (PARTITION BY grantee, owner, table_name, granted_role
                                ORDER BY grantee
                               ) as seqnum_granted_role,
             ROW_NUMBER() OVER (PARTITION BY grantee, owner, table_name, privilege
                                ORDER BY grantee
                               ) as seqnum_privilege
       FROM sys.privs
     ) p
GROUP BY grantee, owner, table_name;