联合三个表并显示数据来自何处（扩展）(Union three tables and show where data came from (extended))

例如，有三个表，每个表都有一列名称

表1 - 列名'名称' - 值'A'，'B'和'C' 表2 - 列名'名称' - 值'A'和'B' 表3 - 列'nane'名称' - 值'A'和'C'

目标是UNION表 - 三个表的每个值应该只显示一次。此外，应该有三个新的“虚拟列”显示包含值的表（当包含值时为“1”，否则为“0”）。所以结果应该是这样的：

Name| Table1 | Table2 | Table3 A | 1 | 1 | 1 B | 1 | 1 | 0 C | 1 | 0 | 1

对此的工作解决方案是：

select Name, max(Table1) as Table1, max(Table2) as Table2, max(Table3) as Table3 from (select Name, 1 as Table1, 0 as Table2, 0 as Table3 from table1 union all select Name, 0 as Table1, 1 as Table2, 0 as Table3 from table2 union all select Name, 0 as Table1, 0 as Table2, 1 as Table3 from table3 ) t group by Name;

现在，应该在表1中再说一个名为“公司”的列，例如

表1 - 列名'名称' - 值'A'，'B'和'C'，列名'公司' - 值'XYZ'，''，'ZYX' 表2 - 列名'名称' - 值'A'和'B' 表3 - 列'nane'名称' - 值'A'和'C'

期望的结果是：

Name | Table1 | Table2 | Table3 | Company A | 1 | 1 | 1 | XYZ B | 1 | 1 | 0 | '' C | 1 | 0 | 1 | ZYX

我没有成功......

There are three tables each with a column of names, for example

Table 1 - column name 'Name' - values 'A', 'B' and 'C' Table 2 - column name 'Name' - values 'A' and 'B' Table 3 - column nane 'Name' - values 'A' and 'C'

The goal is to UNION the tables - each value of the three tables should be shown only one time. In addition there should be three new "virtual columns" showing in which table the value is included('1' when value is included, '0' if not). So the result should look like this:

Name| Table1 | Table2 | Table3 A | 1 | 1 | 1 B | 1 | 1 | 0 C | 1 | 0 | 1

Working solution for this is:

select Name, max(Table1) as Table1, max(Table2) as Table2, max(Table3) as Table3 from (select Name, 1 as Table1, 0 as Table2, 0 as Table3 from table1 union all select Name, 0 as Table1, 1 as Table2, 0 as Table3 from table2 union all select Name, 0 as Table1, 0 as Table2, 1 as Table3 from table3 ) t group by Name;

Now there should be one more column let's say in table 1 called 'Company', for example

Table 1 - column name 'Name' - values 'A', 'B' and 'C', column name 'Company' - values 'XYZ', '', 'ZYX' Table 2 - column name 'Name' - values 'A' and 'B' Table 3 - column nane 'Name' - values 'A' and 'C'

Desired result is:

Name | Table1 | Table2 | Table3 | Company A | 1 | 1 | 1 | XYZ B | 1 | 1 | 0 | '' C | 1 | 0 | 1 | ZYX

I don't succeed...

最满意答案

只需将其添加到每个子查询，然后添加到外部查询：

select Name, max(Table1) as Table1, max(Table2) as Table2, max(Table3) as Table3, max(Company) as Company from (select Name, 1 as Table1, 0 as Table2, 0 as Table3, Company from table1 union all select Name, 0 as Table1, 1 as Table2, 0 as Table3, NULL as Company from table2 union all select Name, 0 as Table1, 0 as Table2, 1 as Table3, NULL as Company from table3 ) t group by Name;

注意：这假定公司每个Name只有一个值。

Just add it to each subquery and then the outer query:

select Name, max(Table1) as Table1, max(Table2) as Table2, max(Table3) as Table3, max(Company) as Company from (select Name, 1 as Table1, 0 as Table2, 0 as Table3, Company from table1 union all select Name, 0 as Table1, 1 as Table2, 0 as Table3, NULL as Company from table2 union all select Name, 0 as Table1, 0 as Table2, 1 as Table3, NULL as Company from table3 ) t group by Name;

Note: this assumes that the company has only one value for each Name.

更多推荐