UNION SELECT不显示所有列

SnakeDoc 发表于 Dev

SnakeDoc

我有几个，VIEW它们嵌套在一个较大VIEW的报告中。

CREATE VIEW view1 AS
    SELECT table1.col1 AS t1c1, table1.col2 AS t1c2
    FROM table1
    WHERE table1.col1 = 0
;

SAMPLE OUTPUT:

SELECT * FROM view1;
+-------------------+
|  t1c1  |   t1c2   |
+--------+----------+
|    0   |  t1stuff |
+--------+----------+
|    0   | t1stuff2 |
+--------+----------+

。

CREATE VIEW view2 AS
    SELECT table2.col1 AS t2c1, table2.col2 AS t2c2
    FROM table2
    WHERE table2.col1 = 5
;

SAMPLE OUTPUT:

SELECT * FROM view2;
+-------------------+
|  t2c1  |   t2c2   |
+--------+----------+
|    5   |  t2stuff |
+--------+----------+
|    5   | t2stuff2 |
+--------+----------+

。

然后，我将它们合并为“报告”视图，如下所示：

CREATE VIEW view3 AS
    SELECT * FROM view1
    UNION
    SELECT * FROM view2
;

SAMPLE OUTPUT:
SELECT * FROM view3;
+-------------------+
|  t1c1  |   t1c2   |
+--------+----------+
|    0   |  t1stuff |
+--------+----------+
|    0   | t1stuff2 |
+--------+----------+
|    5   |  t2stuff |
+--------+----------+
|    5   | t2stuff2 |
+--------+----------+

代替预期：

+-------------------+--------+----------+
|  t1c1  |   t1c2   |  t2c1  |   t2c2   |
+--------+----------+--------+----------+
|    0   |  t1stuff |        |          |
+--------+----------+--------+----------+
|    0   | t1stuff2 |        |          |
+--------+----------+--------+----------+
|        |          |   5    | t2stuff  |
+--------+----------+--------+----------+
|        |          |   5    | t2stuff2 |
+--------+----------+--------+----------+

如何获得预期的结果？

lc。

一个人会问，如果没有数据相关并且甚至不共享列，为什么要在单个查询中执行此操作。但是无论如何，您都必须指定所有列：

select col1 as t1c1, col2 as t1c2, null as t2c1, null as t2c2
from view1
union all
select null as t1c1, null as t1c2, col1 as t2c1, col2 as t2c2
from view2

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。