在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