从多个SELECT子查询中获取数据，以便从MySQL数据库进行报告

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.

非常感谢一如既往。

3 个解决方案

#1

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

#2

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.

正如评论中所提到的，列需要具有相同的名称（您可以使用别名）并保持相同的顺序。

#3

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

#1