SQL自连接去重：实现指定comp与mg的唯一关联

表结构说明 #

表中包含多日期、多sid数据，结构及示例数据如下：

date    | sid | comp | disc
-----------------------
23 june | 1  | az  | 20
23 june | 1  | ph  | 22
23 june | 1  | mg  | 10
23 june | 2  | mg  | 8
23 june | 3  | ph  | 15
23 june | 3  | az  | 11

原查询问题 #

原自连接SQL会将同一date+sid下所有不同comp互相关联，导致大量重复行，且不符合仅关联mg的需求：

select t1.*, t2.comp as comp1, t2.disc as disc1
from table as t1
left join table as t2 on t1.date = t2.date and t1.sid = t2.sid and t1.comp <> t2.comp

解决方案SQL #

要实现仅将非mg的comp关联到同组的mg数据，且避免重复，可先提取每个date+sid对应的mg记录，再左连接原表：

select 
    t1.date,
    t1.sid,
    t1.comp,
    t1.disc,
    case when t1.comp <> 'mg' then t2.comp else null end as comp1,
    case when t1.comp <> 'mg' then t2.disc else null end as disc1
from 
    table as t1
left join (
    -- 子查询筛选每个date+sid对应的mg记录
    select date, sid, comp, disc 
    from table 
    where comp = 'mg'
) as t2 on t1.date = t2.date and t1.sid = t2.sid

逻辑解释 #

1. 子查询t2先过滤出所有comp='mg'的记录，确保每个date+sid只对应一条mg数据（若同一date+sid存在多条mg，可添加聚合函数如max(disc)来确定唯一值）
2. 原表t1与t2仅通过date和sid关联，避免了原自连接中不同非mg值互相关联的情况
3. 用case语句控制：仅当t1.comp不是mg时，才显示t2的mg信息，否则置为null
4. 每个t1记录最多关联一条t2记录，自然不会产生重复行

验证结果 #

执行上述SQL后，将得到符合预期的结果：

date    | sid | comp | disc | comp1 | disc1
-------------------------------------------
23 june | 1  | az  | 20     | mg    | 10
23 june | 1  | ph  | 22     | mg    | 10
23 june | 1  | mg  | 10     | null  | null
23 june | 2  | mg  | 8      | null  | null
23 june | 3  | ph  | 15     | null  | null
23 june | 3  | az  | 11     | null  | null

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