从多个 SELECT 子查询中获取数据以从 MySQL 数据库报告
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15257716/
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
Get data from multiple SELECT sub-queries for reporting from MySQL database
提问by user1775967
I'm trying to achieve is to create one complex query consisting of a few sub-queries. The idea is to give it to a business person to run on a weekly basis to pull reporting data.
我试图实现的是创建一个由几个子查询组成的复杂查询。这个想法是让业务人员每周运行一次以提取报告数据。
The effect would be similar to the query below, where all data from many tables are displayed in one result.
效果类似于下面的查询，其中来自多个表的所有数据都显示在一个结果中。
select * from table1, table2, table3
So I need something like, but it's not working.
所以我需要类似的东西，但它不起作用。
select
(select * from table1 where ...... ) as table1,
(select * from table2 where....... ) as table2
Manually, I could run the sub-queries separately, then manually append the results into one big excel sheet. But I want to make it easier for the business person to do this, and minimize errors.
手动，我可以单独运行子查询，然后手动将结果附加到一张大的 excel 表中。但我想让业务人员更容易做到这一点，并尽量减少错误。
Is this possible in MySQL?
这在 MySQL 中可能吗？
The reason for this is I'm converting a legacy Oracle PIVOT SQL statements into the MySQL equivalence, and the sub-queries are pretty complex.
这样做的原因是我将旧的 Oracle PIVOT SQL 语句转换为 MySQL 等价语句，并且子查询非常复杂。
I can provide the Oracle SQL if needed.
如果需要，我可以提供 Oracle SQL。
Much appreciated as always.
一如既往地受到赞赏。
回答by user1775967
After some fiddling around:
经过一番摆弄之后：
select * from
(select * from table1 where survey_user_id=4 ) as T1
,
(select * from table2 where survey_field_type_id=100 ) as T2
,
(select * from table3 ) as T3
回答by tkeram
If i understand you correctly you just need UNION :D
如果我理解正确，你只需要 UNION :D
(SELECT column1 AS name1, column2 AS name2 FROM table1 WHERE ...... )
UNION
(SELECT column3 AS name1, column4 AS name2 FROM table2 WHERE ...... )
UNION
....
As mentioned bellow in comment, columns need to have the same name (you can use aliases for it) and stay in the same order.
正如下面评论中提到的，列需要具有相同的名称（您可以为其使用别名）并保持相同的顺序。
回答by user3600754
select main.*,
(select col from tbl1 where tbl1.id=main.id) as col1,
(select col from tbl2 where tbl2.id=main.id) as col2,
(select col from tbl3 where tbl3.id=main.id) as col3
from master as main
