Supprimez les lignes en double lors de l'auto-jointure dans SQL

Question

J'essaie de supprimer les valeurs en double des lignes basées sur une jointure sur la même table, mais je ne parviens pas à supprimer une valeur dans une colonne du tableau 1 qui est mappée à la même colonne du tableau 2.

surface: Remarque : Vous n'êtes pas limité à une seule date dans le tableau, il peut y en avoir plusieurs avec plusieurs côtés

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
------------------------

À propos de l'auto-adhésion

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

Résultat attendu : (Ici, j'essaie d'obtenir une combinaison de comp et comp1 où pour chaque sid et date il n'y a que des valeurs de comp différentes mappées avec mg et null dans comp1 si sid n'a que des valeurs mg ou non mg) REMARQUE : Il n'y aura pas de lignes en double lorsque comp est mappé à 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

Voir la base de donnéesfiddle

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