Entfernen Sie doppelte Zeilen, wenn Sie einen Self-Join in SQL durchführen

Question

Ich versuche, doppelte Werte aus Zeilen zu entfernen, die auf einem Join in derselben Tabelle basieren, kann aber keinen Wert in einer Spalte in Tabelle1 entfernen, der derselben Spalte in Tabelle2 zugeordnet ist.

Oberfläche: Hinweis: Sie sind nicht auf nur ein Datum in der Tabelle beschränkt, es können auch mehrere mit mehreren Seiten vorhanden sein

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

Über Selbstbeitritt

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

Erwartetes Ergebnis: (Hier versuche ich, eine Kombination aus comp und comp1 zu erhalten, wobei für jede Sid und jedes Datum nur unterschiedliche Comp-Werte mit mg und null in comp1 zugeordnet sind, wenn Sid nur mg- oder Nicht-mg-Werte hat.) HINWEIS: Es gibt keine doppelten Zeilen, wenn comp comp1 zugeordnet wird

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