Oracle SQL如何查询资源关联审批组及所有所有者、成员并单行输出

预设表结构说明 #

• 资源表 t_resource：资源唯一标识 res_id、资源名称 res_name、关联审批组ID group_id 及其他资源属性字段
• 审批组表 t_approval_group：审批组唯一标识 group_id、组名称 group_name 及其他组属性字段
• 审批组所有者表 t_group_owner：关联审批组ID group_id、所有者名称/账号 owner_name
• 审批组成员表 t_group_user：关联审批组ID group_id、成员名称/账号 user_name

核心实现代码 #

SELECT
  r.res_id,
  r.res_name,
  g.group_id,
  g.group_name,
  -- 聚合所有审批组所有者为同一列，逗号分隔
  LISTAGG(own.owner_name, ',') WITHIN GROUP (ORDER BY own.owner_name) AS group_owners,
  -- 聚合所有审批组成员为单独一列，逗号分隔
  LISTAGG(usr.user_name, ',') WITHIN GROUP (ORDER BY usr.user_name) AS group_users
FROM
  t_resource r
  -- 1:1关联资源与审批组
  INNER JOIN t_approval_group g ON r.group_id = g.group_id
  -- 左连接所有者表，无所有者时不会过滤主数据
  LEFT JOIN t_group_owner own ON g.group_id = own.group_id
  -- 左连接成员表，无成员时不会过滤主数据
  LEFT JOIN t_group_user usr ON g.group_id = usr.group_id
-- 按资源、审批组维度分组，保证单个资源对应单行结果
GROUP BY
  r.res_id,
  r.res_name,
  g.group_id,
  g.group_name;

特殊场景适配 #

• 需去重聚合内容时，可在LISTAGG中加入DISTINCT关键字：LISTAGG(DISTINCT own.owner_name, ',') WITHIN GROUP (ORDER BY own.owner_name)
• 聚合内容长度超过VARCHAR2 4000字符限制时，改用XMLAGG实现，支持CLOB类型大内容存储，示例写法：

  RTRIM(XMLAGG(XMLELEMENT(E, own.owner_name, ',').EXTRACT('//text()') ORDER BY own.owner_name).GETCLOBVAL(), ',') AS group_owners
  

• Oracle 19c及以上版本可直接在LISTAGG中添加溢出截断规则，避免长度报错：LISTAGG(own.owner_name, ',' ON OVERFLOW TRUNCATE '...') WITHIN GROUP (ORDER BY own.owner_name)

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