在SQL中进行自连接时，去除重复行-PHP中文网问答

我试图根据同一个表上的联接从行中删除重复的值，但无法删除映射到表2 中同一列的 table1 中的一列中的一个值。

表：注意：不限于表中只有一个日期，可以有多个具有多个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
------------------------

关于自加入

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

Output from above query:

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

预期结果：（这里我尝试获取 comp 和 comp1 的组合，其中对于每个 sid 和日期，仅与 mg 映射的不同 comp 值，如果 sid 仅具有 mg 或非 mg 值，则 comp1 中为 null）注意：comp 映射到 comp1 时不会出现重复行

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