在SQL中进行自连接时，去除重复行

Question

我试图根据同一个表上的联接从行中删除重复的值，但无法删除映射到表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

Answer 1

查看 dbfiddle

WITH cte AS (
SELECT t1.*, t2.comp as comp1, t2.disc as disc1, SUM(1) OVER(PARTITION BY date,sid,comp) AS cnt
FROM `table` t1
LEFT JOIN `table` t2 ON t1.date = t2.date AND t1.sid = t2.sid AND t1.comp  t2.comp 
)

SELECT 
  date, sid, comp, disc, 
  CASE WHEN comp1  'mg' THEN NULL ELSE comp1 END AS comp1, 
  CASE WHEN comp1  'mg' THEN NULL ELSE disc1 END AS disc1
FROM cte
WHERE
 (CASE WHEN comp  'mg' OR comp1 IS NULL THEN cnt END) = 1
 OR 
 (CASE WHEN comp  'mg' AND comp1 = 'mg' THEN cnt END) >= 2