如何创建“即时”交易流程PostgreSQL中的SELECT语句中的映射表 [英] How to create an "on-the-fly" mapping table within a SELECT statement in Postgresql

问题描述

我正在创建一个选择语句，该语句结合两个表，区域和输出，基于
在引用的设备表上，以及在 zone_number 到 output_type_id 。
zone_number 到 output_type_id 的映射在数据库中的任何位置都不会出现
，并且我想在选择的
语句中即时创建它。下面是我的架构：

I'm creating a select statement that combines two tables, zone and output, based on a referenced device table and on a mapping of zone_number to output_type_id. The mapping of zone_number to output_type_id doesn't appear anywhere in the database, and I would like to create it "on-the-fly" within the select statement. Below is my 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)
);

这是一些示例数据：

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 。对于此示例：

I need to get the associated enabled field from the output table for each zone for a given device. Each zone_number maps to an output_type_id. For this example:

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

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

One way to handle the mapping would be to create a new table

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获取所有区域，并启用 标志，用于设备1：

And use the following SQL to get all zones, plus the enabled flag, for device 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

但是，我正在寻找一种创建映射的方法的区域nunbers可以输出
类型，而无需创建新表，也无需将AND / OR
语句拼凑在一起。是否有一种优雅的方法在select语句中的两个字段
之间创建映射？

However, I'm looking for a way to create the mapping of zone nunbers to output types without creating a new table and without piecing together a bunch of AND/OR statements. Is there an elegant way to create a mapping between the two fields within the select statement? Something like:

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
表中。但是，我对此没有控制权。我只是从应用程序方面寻找
最优雅的解决方案。谢谢！

Disclaimer: I know that ideally the enabled field would exist in the zone table. However, I don't have control over that piece. I'm just looking for the most elegant solution from the application side. Thanks!

推荐答案

您可以将 VALUES 用作内联表并加入为此，您只需要给它一个别名和列名即可：

You can use VALUES as an inline table and JOIN to it, you just need to give it an alias and column names:

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

从精细手册：




VALUES 也可用于子<< c $ c $可能会在 FROM 子句中以
为例编写c> SELECT ：

VALUES can also be used where a sub-SELECT might be written, for example in a FROM clause:

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;

这篇关于如何创建“即时”交易流程PostgreSQL中的SELECT语句中的映射表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！