So erstellen Sie eine "on-the-fly" mapping-Tabelle innerhalb einer SELECT-Anweisung in Postgresql
Ich bin der Erstellung einer select-Anweisung verbindet die zwei Tabellen, zone
und output
,
auf Basis einer referenzierten device
Tabelle und eine Abbildung von zone_number
zu output_type_id
.
Die Zuordnung von zone_number
zu output_type_id
nicht angezeigt
überall in der Datenbank, und ich möchte, um es zu schaffen "on-the-fly" innerhalb der select -
- Anweisung. Unten ist mein schema:
CREATE TABLE output_type (
id INTEGER NOT NULL,
name TEXT,
PRIMARY KEY (id)
);
CREATE TABLE device (
id INTEGER NOT NULL,
name TEXT,
PRIMARY KEY (id)
);
CREATE TABLE zone (
id SERIAL NOT NULL,
device_id INTEGER NOT NULL REFERENCES device(id),
zone_number INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (zone_number)
);
CREATE TABLE output (
id SERIAL NOT NULL,
device_id INTEGER NOT NULL REFERENCES device(id),
output_type_id INTEGER NOT NULL REFERENCES output_type(id),
enabled BOOLEAN NOT NULL,
PRIMARY KEY (id)
);
Ist und hier einige Beispiel-Daten:
INSERT INTO output_type (id, name) VALUES
(101, 'Output 1'),
(202, 'Output 2'),
(303, 'Output 3'),
(404, 'Output 4');
INSERT INTO device (id, name) VALUES
(1, 'Test Device');
INSERT INTO zone (device_id, zone_number) VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4);
INSERT INTO output (device_id, output_type_id, enabled) VALUES
(1, 101, TRUE),
(1, 202, FALSE),
(1, 303, FALSE),
(1, 404, TRUE);
Ich brauche, um die damit verbundenen enabled
Feld aus der Ausgabe-Tabelle für jede zone, die für ein bestimmtes Gerät.
Jeder zone_number
Karten zu einer output_type_id
. Für dieses Beispiel:
zone_number | output_type_id
----------------------------
1 | 101
2 | 202
3 | 303
4 | 404
Einer Weise zu handhaben, die Zuordnung wäre eine neue Tabelle zu erstellen
CREATE TABLE zone_output_type_map (
zone_number INTEGER,
output_type_id INTEGER NOT NULL REFERENCES output_type(id)
);
INSERT INTO zone_output_type_map (zone_number, output_type_id) VALUES
(1, 101),
(2, 202),
(3, 303),
(4, 404);
Und verwenden Sie die folgenden SQL um alle Zonen, plus die enabled
Flagge, für Gerät 1:
SELECT zone.*, output.enabled
FROM zone
JOIN output
ON output.device_id = zone.device_id
JOIN zone_output_type_map map
ON map.zone_number = zone.zone_number
AND map.output_type_id = output.output_type_id
AND zone.device_id = 1
Jedoch, ich bin auf der Suche nach ein Weg, um die Zuordnung der zone nunbers Ausgabe
Arten ohne eine neue Tabelle erstellen und ohne piecing zusammen eine Reihe von UND/ODER
Aussagen. Ist es ein eleganter Weg, um erstellen Sie eine Zuordnung zwischen den beiden Feldern
innerhalb der select-Anweisung? So etwas wie:
SELECT zone.*, output.enabled
FROM zone
JOIN output
ON output.device_id = zone.device_id
JOIN (
SELECT (
1 => 101,
2 => 202,
3 => 303,
4 => 404
) (zone_number, output_type_id)
) as map
ON map.zone_number = zone.zone_number
AND map.output_type_id = output.output_type_id
AND zone.device_id = 1
Disclaimer: ich weiß, dass im Idealfall die enabled
Bereich existieren würde, in der zone
Tabelle. Allerdings habe ich nicht die Kontrolle über das Stück. Ich bin gerade auf der Suche für die
die meisten elegante Lösung von der Anwendung her. Danke!
- Vielleicht könnten Sie einen BLICK?
- Würde ich OK mit, dass, aber die zugrunde liegende mapping müsste noch festgelegt werden innerhalb einer select-Anweisung (in der Ansicht), und das ist, wo es bricht für mich. Wie es scheint, sollte es eine einfache Möglichkeit zu schaffen, ein Schlüssel/Wert "hash", dass kann verwendet werden innerhalb einer select-Anweisung verknüpfen der beiden Tabellen.
- Ihre
output_type
Beispiel-Daten nicht mit Ihremoutput. output_type_id
die erste verwendet, 101, 102, 103 und 104, während die zweite verwendet, 101, 202, 303 und 404; Hurra für FKs für den Hinweis auf mich 🙂 - Vielen Dank für die catch @mu! Ich fest, dass in meiner Frage.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Können Sie
VALUES
als eine inline-Tabelle und der JOIN-Sie müssen nur geben Sie einen alias und Spaltennamen:Aus der Handbuch:
Also nur zur Ergänzung der Antwort akzeptiert, wird der folgende code ist ein Gültiger, eigenständige Postgresql-Ausdruck evaluiert zu einer 'inline' Beziehung mit Spalten
(zone_number, output_type_id)
:(Die
(VALUES ... AS ...)
Teil alleine nicht ausreicht, um einen gültigen Ausdruck, das ist, warum ich fügte hinzu, dieSELECT * FROM
.)