在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

查看 db<>fiddle

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