组合多个 SELECT 语句 [英] Combine multiple SELECT statements

问题描述

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

select result from foo.table limit 1;从 bar.table 限制 1 中选择结果；从 doo.table 限制 1 中选择结果；

(foo, bar & doo 是我的模式的例子，实际上有数百个).

每个 SELECT 将只返回一个结果.我只想要一列 result 与模式一样多的行.然后我可以根据架构名称将其复制回 Excel.

当我运行上面的查询时，我得到 1 行，其他行被丢弃:

<前>丢弃 1 行的查询结果.丢弃 1 行的查询结果.总查询运行时间:40 毫秒.检索到 1 行.

我曾尝试使用 UNION ALL，但是我用来确保每个架构表只返回一行的 limit 1 似乎阻止了这种工作.

如何防止其他行被丢弃，或者编写一个查询，以更有效的方式返回我需要的值(两列 - schema_name，结果 - 每个模式一行)?

解决方案

将各个子语句括在括号中，使语法无歧义:

(SELECT result FROM tbl1 LIMIT 1)联合所有(从 tbl2 LIMIT 1 中选择结果)

关于UNION的手册 对此事非常清楚:

<块引用>

select_statement 是任何没有 ORDER BY、LIMIT 的 SELECT 语句，FOR UPDATE 或 FOR SHARE 子句.(ORDER BY 和 LIMIT 可以附加如果子表达式被括在括号中.没有括号中，这些条款将被视为适用于UNION，而不是它的右侧输入表达式.)

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 are examples of my schemas, there are hundreds in reality).

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.

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.

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.

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?

解决方案

Wrap individual sub-statements in parenthesis to make the syntax unambiguous:

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

The manual about UNION is very clear on the matter:

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

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