Setze den Spaltenwert, wenn der bestimmte Datensatz in derselben Tabelle vorhanden ist

Question

Wie könnte ich eine Ansicht für eine Tabelle mit einer Teilmenge der Datensätze der Tabelle, allen Spalten der Tabelle und einer zusätzlichen "Flag"-Spalte erstellen, deren Wert auf 'X' gesetzt wird, wenn die Tabelle einen bestimmten Typ von Datensatz enthält? Betrachten Sie beispielsweise die folgende Beziehungstabelle Relations, bei der die Werte für Typen stehen für 'H'-Mensch, 'D'-Hund:

 id | type | relation | related
--------------------------------
 H1 |  H   | weiß     | D2 
 H1 |  H   | besitzt  | D2 
 H2 |  H   | weiß     | D1 
 H2 |  H   | besitzt  | D1
 H3 |  H   | weiß     | D1
 H3 |  H   | weiß     | D2
 H3 |  H   | behandelt| D1
 H3 |  H   | behandelt| D2
 D1 |  D   | beißt    | H3
 D2 |  D   | beißt    | H3

Es kann keine bestimmte Reihenfolge von Datensätzen in dieser Tabelle geben.

Ich möchte eine Ansicht Humans erstellen, die alle Mensch-zu-Hund-weiß-Beziehungen aus Relations und alle Spalten von Relations sowie eine zusätzliche Spalte isOwner enthält, die 'X' speichert, wenn ein Mensch in einer gegebenen Beziehung jemanden besitzt:

 id | type | relation | related | isOwner
------------------------------------------
 H1 |  H   | weiß     |    D2   |    X
 H2 |  H   | weiß     |    D1   |    X
 H3 |  H   | weiß     |    D1   |    

aber habe damit ein bisschen zu kämpfen. Kennst du einen Weg, dies zu tun, vorzugsweise in einem CREATE VIEW-Aufruf oder auf irgendeine Weise wirklich?

Answer 1

ERSTELLE ANSICHT vHumanDogRelations
ALS
AUSWÄHLEN
    id,
    typ,
    beziehung,
    verwandt,
    -- In Betracht ziehen, anstelle 'X' 0/1 zu verwenden
    FALLS 
        WENN EXISTS (
            AUSWÄHLEN 1
            VON Beziehungen
            WO 
                id = r.id
                UND verwandt = r.verwandt -- Gehört jemandem oder nur dieser Verwandte?
                UND beziehung = 'besitzt'    
        ) DANN 'X'
        SONST ''
    END ALS istBesitzer
VON Beziehungen r
WO 
    beziehung = 'kennt'
    UND typ = 'H'
    UND verwandt = 'D';

Answer 2

Sie sollten in der Lage sein, das folgende select in die Ansichtsdefinition einzufügen

select *, case when exists 
(select * from Relations where id = r.id and relation= 'owns') then 'X'
else '' end as isOwner 
from Relations r

Answer 3

Sie könnten auch PIVOT verwenden, um das gewünschte Ergebnis zu erzielen. Ich werde die Methode im Detail erklären, da die endgültige Abfrage möglicherweise verwirrend erscheint.

Entnehmen Sie zunächst eine Teilmenge von Relation, bei der type H ist und relation entweder knows oder owns ist, wobei der Wert owns durch X ersetzt wird:

SELECT
  id,
  type,
  relation = CASE relation WHEN 'owns' THEN 'X' ELSE relation END,
  related
FROM Relations
WHERE type = 'H'
  AND relation IN ('knows', 'owns')

Ausgehend von Ihrem Beispiel erhalten Sie dies:

id  type  relation  related
--  ----  --------  -------
H1   H    knows     D2
H1   H    owns      D2
H2   H    knows     D1
H2   H    owns      D1
H3   H    knows     D1
H3   H    knows     D2

Wenden Sie als nächstes diese PIVOT-Klausel auf das Ergebnis der ersten Abfrage an:

PIVOT (
  MAX(relation) FOR relation IN (knows, X)
) AS p

Dadurch werden Zeilen mit identischen id, type, related Werten zu einer einzigen Zeile gruppiert und relation in zwei Spalten aufgeteilt, knows und X:

id  type  related  knows  X
--  ----  -------  -----  ----
H1   H    D2       knows  X
H2   H    D1       knows  X
H3   H    D1       knows  NULL
H3   H    D2       knows  NULL

An diesem Punkt müssen Sie nur noch den Spalten Satz leicht für die Ausgabe in der Haupt SELECT-Klausel umstellen, wobei knows in relation und X in isOwner umbenannt werden:

SELECT
  id,
  type,
  relation = knows,
  related,
  isOwner = X
...

Ausgabe:

id  type  relation  related  isOwner
--  ----  --------  -------  -------
H1   H    knows     D2       X
H2   H    knows     D1       X
H3   H    knows     D1       NULL
H3   H    knows     D2       NULL

NULL-Werte können natürlich leicht durch leere Zeichenfolgen ersetzt werden, falls erforderlich.

Ein letzter Schliff könnte sein, zu der Hauptabfrage diesen zusätzlichen Filter hinzuzufügen:

WHERE knows IS NOT NULL

nur für den Fall, dass es Leute gibt, die Hunde besitzen, ohne sie tatsächlich zu kennen (und Sie wollen diese nicht im Ergebnis haben).

Die vollständige Abfrage würde also wie folgt aussehen:

SELECT
  id,
  type,
  relation = knows,
  related,
  isOwner = X
FROM (
  SELECT
    id,
    type,
    relation = CASE relation WHEN 'owns' THEN 'X' ELSE relation END,
    related
  FROM Relations
  WHERE type = 'H'
    AND relation IN ('knows', 'owns')
) AS s
PIVOT (
  MAX(relation) FOR relation IN (knows, X)
) AS p
WHERE knows IS NOT NULL
;

Ein SQL Fiddle-Demo für diese Lösung finden Sie hier.