如何在Postgresql中的SELECT语句中创建“即时”映射表

Question

我创建结合两个表的SELECT语句，zone和output的基础上，参照device表，并在一个映射zone_number到output_type_id。映射zone_number到output_type_id未在数据库中的任何地方出现，我想创建“关于即时”的SELECT语句中。以下是我的架构：

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

这是一些示例数据：

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

我需要enabled从输出表中获取给定设备每个区域的关联字段。每个zone_number映射到一个output_type_id。对于此示例：

zone_number | output_type_id
----------------------------
1           | 101
2           | 202
3           | 303 
4           | 404

处理映射的一种方法是创建一个新表

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

并使用以下SQL获取enabled设备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

但是，我正在寻找一种创建区域编号到输出类型的映射的方法，而无需创建新表并且不将AND /
OR语句拼凑在一起。是否有一种优雅的方法在select语句中的两个字段之间创建映射？就像是：

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

免责声明：我知道理想情况下该enabled字段将存在于zone 表中。但是，我对此没有控制权。我只是在寻找应用程序方面最优雅的解决方案。谢谢！

Answer 1

您可以将其VALUES用作内联表并加入其中，只需为其提供别名和列名即可：

join (values (1, 101), (2, 202), (3, 303), (4, 304)) as map(zone_number, output_type_id)
on ...

从精美的手册中：

VALUES也可以在SELECT可能写有FROM子句的地方使用，例如在子句中：

SELECT f.*
  FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t

(studio, kind)
WHERE f.studio = t.studio AND f.kind = t.kind;

UPDATE employees SET salary = salary * v.increase
  FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target,

increase)
WHERE employees.depno = v.depno AND employees.sales >= v.target;