MySQL Left outer join查询返回重复行问题的修复方案求助

问题根因 #

你遇到的重复行问题确实由用户在teamusers表中存在多条记录导致，核心问题出在原查询直接左连接了完整的teamusers表：当单个用户属于多个团队时，单条业务记录会匹配到多条teamusers行，最终生成重复的查询结果。同时原查询中统计团队数量的子查询还冗余关联了supconvers_querydtls表，存在额外的性能开销。

修复方案 #

无需多次关联teamusers、portalteam表，直接把团队数量统计、单团队描述查询的逻辑合并到同一个子查询中，即可避免多对一关联生成重复行，同时简化查询逻辑。

修复后的完整查询语句如下：

select
  sc.supplier,
  sq.query_value,
  sc.cnv_name as name,
  sc.cnv_id,
  sq.query_type_id,
  qt.query_type_desc,
  date_format(sq.query_date, '%d/%m/%Y') as query_date,
  qs.query_status,
  sq.sla_count,
  sq.sla_state,
  sq.assigned_to,
  IfNull(teams.team_count, 0) team_count,
  if(
    sq.sla_state is null
    OR sq.sla_state = '',
    '',
    if(
      sq.sla_state = 0,
      'Query Handler',
      if(
        sq.sla_state = 1,
        'Team Leader',
        if(
          sq.sla_state = 2,
          'Manager',
          if(
            sq.sla_state = 3,
            'Senior Manager',
            'Senior Manager (GSCOP BREACH)'
          )
        )
      )
    )
  ) as sla_state_desc,
  if(
    IfNull(teams.team_count, 0) <= 0,
    '',
    if(teams.team_count > 1, '(multiple)', teams.single_team_desc)
  ) as team
from
  supconversation sc
  left outer join supconvers_querydtls sq on sq.cnv_id = sc.cnv_id
  left outer join querytype qt on qt.query_type_id = sq.query_type_id
  left outer join querystatus qs on qs.query_status_id = sq.query_status_id
  -- 重构后的团队统计子查询，同时返回单团队描述
  left outer join (
    select
      count(*) team_count,
      tu.user_id,
      max(pt.team_desc) as single_team_desc
    from
      teamusers tu
      left outer join portalteam pt on pt.team_id = tu.team_id
    group by tu.user_id
  ) teams on teams.user_id = sq.assigned_to
where
  sc.supplier = 'SUPTEST1'
  and sc.cnv_type = 'Q'
order by
  query_date desc

内容的提问来源于stack exchange，提问作者J.Do