如何在SQL查询中将多角色多邮箱合并为单条目避免笛卡尔积？

当前你的SQL查询因为同时关联了邮箱表（goremal）和角色表（gorirol）这两个多对多关联的表，导致产生笛卡尔积——比如一个用户有5个角色和5个邮箱时，会返回25条重复的用户数据。可以通过先对多值字段做聚合，再关联主表的方式解决，同时将同一用户的所有邮箱、角色合并为单个字段。

解决方案（以Oracle为例，其他数据库可替换聚合函数） #

核心思路是先对邮箱和角色分别进行分组聚合，生成每个用户的邮箱列表、角色列表，再将这些聚合结果与主表关联，避免直接关联多对多表产生笛卡尔积。

修改后的SQL代码如下：

SELECT
  s.spriden_id AS cwid,
  s.spriden_first_name AS "first_name",
  s.spriden_last_name AS "last_name",
  g.gobtpac_external_user AS "username",
  '***-**-' || substr(p.spbpers_ssn, -4) AS "ssn",
  to_char(p.spbpers_birth_date, 'MON-DD-YYYY') AS "dob",
  p.spbpers_pref_first_name AS "prefname",
  -- 合并邮箱为逗号分隔的字符串，去重并排序
  COALESCE(e.email_list, '') AS "email",
  -- 合并角色为逗号分隔的字符串，去重并排序
  COALESCE(r.role_list, '') AS "Role",
  i.gobintl_passport_id AS "Passport ID",
  pe.pebempl_last_work_date AS "contract end date"
FROM
  gobtpac g
INNER JOIN saturn.spriden s ON g.gobtpac_pidm = s.spriden_pidm
INNER JOIN saturn.spbpers p ON s.spriden_pidm = p.spbpers_pidm
LEFT JOIN (
  -- 聚合每个用户的邮箱
  SELECT
    goremal_pidm,
    LISTAGG(DISTINCT goremal_email_address, ', ') WITHIN GROUP (ORDER BY goremal_email_address) AS email_list
  FROM general.goremal
  WHERE goremal_emal_code IN ('CO', 'AL', 'OK', 'BU', 'PE')
    AND goremal_status_ind = 'A'
  GROUP BY goremal_pidm
) e ON s.spriden_pidm = e.goremal_pidm
LEFT JOIN (
  -- 聚合每个用户的角色
  SELECT
    gorirol_pidm,
    LISTAGG(DISTINCT gorirol_role, ', ') WITHIN GROUP (ORDER BY gorirol_role) AS role_list
  FROM general.gorirol
  WHERE gorirol_role_group = 'INTCOMP'
  GROUP BY gorirol_pidm
) r ON s.spriden_pidm = r.gorirol_pidm
LEFT JOIN general.gobintl i ON s.spriden_pidm = i.gobintl_pidm
LEFT JOIN payroll.pebempl pe ON s.spriden_id = pe.pebempl_pidm
WHERE
  s.spriden_change_ind IS NULL
  -- 原LIKE '%'条件无意义，可移除或替换为实际过滤规则
ORDER BY
  "last_name",
  "first_name",
  cwid

不同数据库的聚合函数替换说明 #

如果你的数据库不是Oracle，替换对应的字符串聚合函数即可：

• PostgreSQL: 使用STRING_AGG(DISTINCT 字段名, ', ' ORDER BY 字段名)
• MySQL/MariaDB: 使用GROUP_CONCAT(DISTINCT 字段名 ORDER BY 字段名 SEPARATOR ', ')
• SQL Server: 使用STRING_AGG(DISTINCT 字段名, ', ') WITHIN GROUP (ORDER BY 字段名)（SQL Server 2017+支持）

关键优化点 #

1. 移除了原查询中的DISTINCT，因为聚合后不会再产生重复数据
2. 将邮箱和角色的过滤条件移到子查询中，减少主查询的数据量
3. 使用COALESCE处理无邮箱/角色的用户，避免返回NULL值

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