PostgreSQL结合了多个选择语句 [英] PostgreSQL combine multiple select statements

问题描述

我已经使用Excel从具有大量相同模式的数据库的模式名称列表中生成了许多 SELECT 语句：

I've used Excel to generate numerous SELECT statements from a list of the schema names from a database with a large number of identical schemas:

select result from foo.table limit 1;
select result from bar.table limit 1;
select result from doo.table limit 1;

（ foo ， bar & doo 是我的架构的示例，实际上有数百个。）

(foo, bar & doo are examples of my schemas, there are hundreds in reality).

每个 SELECT 仅返回一个结果。我只希望一列 result 具有与模式一样多的行。然后，我可以根据模式名称将其复制回Excel。

Each SELECT will return only one result. I simply want one column result with as many rows as there are schemas. I can then copy this back into Excel against the schema names.

在上面运行查询时，我得到1行，而其他行被丢弃：

When I run the query above I get 1 row, with the others being discarded:

Query result with 1 row discarded.

Query result with 1 row discarded.

Total query runtime: 40 ms.
1 row retrieved.

我尝试使用 UNION ALL ，但限制1 我用来确保每个模式表中仅返回一行，以防止其工作。

I have tried using UNION ALL, but the limit 1 I am using to ensure one row only is returned from each schema table appears to prevent this from working.

我怎么能防止其他行不会被丢弃，或者编写查询以更有效的方式返回我需要的值（两列-schema_name，结果-每个模式一行）？

How can I either prevent the other rows from being discarded, or write a query that will return the values I need (two columns - schema_name, result - one row for each schema) in a more efficient way?

推荐答案

只需将单个语句括在括号中即可使语法清晰：

Just wrap individual statements in parenthesis to make the syntax unambiguous:

(SELECT result FROM tbl1 LIMIT 1)
UNION ALL
(SELECT result FROM tbl2 LIMIT 1)

关于的手册UNION 在此问题上非常清楚：

The manual about UNION is very clear on the matter:

select_statement 是任何 SELECT 语句，而没有 ORDER BY ， LIMIT ，
用于更新或 FOR SHARE 子句。 （ ORDER BY 和 LIMIT 可以在子表达式后附加
到括号中。如果没有
括号，这些子句将应用于
UNION 的结果，而不是其右手输入表达式。）

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

这篇关于PostgreSQL结合了多个选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！