oracle 将两个表与不同的列组合在一起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9015127/
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
Combining two tables with a different column
提问by Mouna Cheikhna
I have to select requests that i want to combine using UNION :
我必须选择要使用 UNION 组合的请求：
Table 1: which is a join between Table_a, table_b and table_c
表 1：这是 Table_a、table_b 和 table_c 之间的连接
id_table_a desc_table_a table_b.id_user table_c.field
-----------------------------------------------------------
1 desc1 1 field1
2 desc2 2 field2
3 desc3 3 field3
Table 2: which is also a join between Table_a, table_b and table_c but it has these columns:
表 2：它也是 Table_a、table_b 和 table_c 之间的连接，但它具有以下列：
id_table_a desc_table_a table_c.id_user table_c.field
-----------------------------------------------------------
4 desc4 4 field4
5 desc5 5 field8
9 desc9 6 field9
the difference between the two is that in Table1 we have table_b.id_userand table two
table_c.id_userinstead .
两者之间的区别在于，在 Table1 中我们有table_b.id_user和 table 2
table_c.id_user代替。
Combined Table
组合表
id_table_a desc_table_a id_user table_c.field
-----------------------------------------------------------
1 desc1 1 field1
2 desc2 2 field2
3 desc3 3 field3
4 desc4 4 field4
5 desc5 5 field5
9 desc9 6 field6
I already have the join requests working but doing union between the two gives me
我已经有加入请求工作，但在两者之间进行联合给了我
ORA-01790 expression must have same datatype as corresponding expression
which make sense because the two columns are not the same .
这是有道理的，因为两列不一样。
Im using zend_Db's join and union for this .
我为此使用了 zend_Db 的 join 和 union。
So how can i tackle this to get the result ?
那么我该如何解决这个问题以获得结果呢？
Thanks.
谢谢。
回答by John Woo
Are the results above the same as the sequence of columns in your table? because oracle is strict in column orders. this example below produces an error:
上面的结果是否与表中列的顺序相同？因为oracle在列顺序上很严格。下面的这个例子产生了一个错误：
create table test1_1790 (
col_a varchar2(30),
col_b number,
col_c date);
create table test2_1790 (
col_a varchar2(30),
col_c date,
col_b number);
select * from test1_1790
union all
select * from test2_1790;
ORA-01790: expression must have same datatype as corresponding expression
ORA-01790: 表达式必须与相应的表达式具有相同的数据类型
As you see the root cause of the error is in the mismatching column ordering that is implied by the use of * as column list specifier. This type of errors can be easily avoided by entering the column list explicitly:
如您所见，错误的根本原因在于使用 * 作为列列表说明符隐含的不匹配的列排序。通过明确输入列列表，可以轻松避免此类错误：
select col_a, col_b, col_c from test1_1790
union all
select col_a, col_b, col_c from test2_1790;
A more frequent scenario for this error is when you inadvertently swap (or shift) two or more columns in the SELECT list:
此错误更常见的情况是当您无意中交换（或移动）SELECT 列表中的两列或更多列时：
select col_a, col_b, col_c from test1_1790
union all
select col_a, col_c, col_b from test2_1790;
OR if the above does not solve your problem, how about creating anALIASin the columnslike this: (the query is not the same as yours but the point here is how to add alias in the column.)
或者，如果上述方法不能解决您的问题，那么如何ALIAS在列中创建这样的 :（查询与您的不同，但这里的重点是如何在列中添加别名。）
SELECT id_table_a,
desc_table_a,
table_b.id_user as iUserID,
table_c.field as iField
UNION
SELECT id_table_a,
desc_table_a,
table_c.id_user as iUserID,
table_c.field as iField
hope this helps.
希望这可以帮助。
