如何在 Postgresql 的 SELECT 语句中创建“即时”映射表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17532603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create an "on-the-fly" mapping table within a SELECT statement in Postgresql
提问by Divey
I'm creating a select statement that combines two tables, zoneand output,
based on a referenced devicetable and on a mapping of zone_numberto output_type_id.
The mapping of zone_numberto output_type_iddoesn't appear
anywhere in the database, and I would like to create it "on-the-fly" within the select
statement. Below is my schema:
我正在创建一个 select 语句，它结合了两个表zone和output，基于引用的device表和zone_numberto的映射output_type_id。zone_numberto的映射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)
);
And here is some example data:
这是一些示例数据：
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);
I need to get the associated enabledfield from the output table for each zone for a given device.
Each zone_numbermaps to an output_type_id. For this example:
我需要enabled从给定设备的每个区域的输出表中获取相关字段。每个zone_number映射到一个output_type_id. 对于这个例子：
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);
And use the following SQL to get all zones, plus the enabledflag, for device 1:
并使用以下 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
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:
但是，我正在寻找一种方法来创建区域编号到输出类型的映射，而无需创建新表，也无需将一堆 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
Disclaimer: I know that ideally the enabledfield would exist in the zonetable. However, I don't have control over that piece. I'm just looking for the
most elegant solution from the application side. Thanks!
免责声明：我知道理想情况下该enabled字段将存在于zone表中。但是，我无法控制那件作品。我只是从应用程序方面寻找最优雅的解决方案。谢谢！
回答by mu is too short
You can use VALUESas an inline table and JOIN to it, you just need to give it an alias and column names:
您可以将其VALUES用作内联表并加入它，您只需要给它一个别名和列名：
join (values (1, 101), (2, 202), (3, 303), (4, 304)) as map(zone_number, output_type_id)
on ...
From the fine manual:
从精美的手册：
VALUEScan also be used where a sub-SELECTmight be written, for example in aFROMclause: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;
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;
回答by Valentin Waeselynck
So just to complement the accepted answer, the following code is a valid, self-containedPostgresql expression which will evaluate to an 'inline' relation with columns (zone_number, output_type_id):
因此，为了补充已接受的答案，以下代码是一个有效的、自包含的Postgresql 表达式，它将评估为与列的“内联”关系(zone_number, output_type_id)：
SELECT * FROM
(VALUES
(1, 101),
(2, 202),
(3, 303),
(4, 304)
) as i(zone_number, output_type_id)
(The (VALUES ... AS ...)part alone will not make a valid expression, which is why I added the SELECT * FROM.)
（(VALUES ... AS ...)单独的部分不会产生有效的表达式，这就是为什么我添加了SELECT * FROM.）
回答by Francisco Soto
JOIN
(SELECT 1 zone_number, 101 as output_type_id
UNION ALL
SELECT 2 zone_number, 202 as output_type_id
UNION ALL
SELECT 3 zone_number, 303 as output_type_id
) mappings on mappings.zone_number = zone.zone_number
