oracle 组合和连接两个不同编号的表。列和相同的列名
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/346138/
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 and joining two tables with different no. of columns and same column name
提问by Shiel
I tried to combined tables which is fus_shift and root tableinto a new table which is final tablebut it outputs like "ERROR at line 2:
ORA-01789: query block has incorrect number of result columns". I tried also joining table as my alternative but it also outputs "ERROR at line 3: ORA-00918: column ambiguously defined". Is there another way to do combining and joining two table with different number of columns and having the same column name respectively? Thanks again:-)
code error:
create table final as
select * from fus_shift
union
select * from root;
我尝试将fus_shift 和根表的表合并到一个新表中,该表是最终表,但它输出类似“第 2 行的错误:ORA-01789:查询块的结果列数不正确”。我也尝试加入表作为我的替代方案,但它也输出“第 3 行的错误:ORA-00918:列模糊定义”。是否有另一种方法来组合和连接具有不同列数且分别具有相同列名的两个表?再次感谢:-)
代码错误:
create table final as
select * from fus_shift
union
select * from root;
code error:
select record_num,test_num,t_date,t_time,system_type,category,comments,val
from fus_shiftrpt,root
where record_num=record_num;
代码错误:
从 fus_shiftrpt,root
where record_num=record_num
选择 record_num,test_num,t_date,t_time,system_type,category,comments,val
my tables:
我的表:
fus_shift Table
Record_Num test date time system
-----------------------------------------------------------
1 test15 08-11-12 13:20:01 sys23
2 test17 08-11-03 14:24:00 sys24
3 test13 08-11-13 17:25:04 sys45
4 test15 08-11-14 18:24:00 sys67
5 test16 08-11-15 19:24:06 sys45
root Table
Record_Num category comments validated by
---------------------------------------------------
1 dirt checked admin
2 prog checked admin
3 dirt checked pe
4 wires checked ee
5 prog repair admin
emphasized text
强调文本
回答by MOZILLA
You certainly cannot apply "Union" to your tables. It can be applied only if both queries return same number (and of similar type) of columns.
您当然不能将“联合”应用于您的表格。仅当两个查询都返回相同数量(且类型相似)的列时才可以应用它。
You can join the two tables but would have to use "table alias" while joining since "record_num" field is common in both the tables. Here is the query that would work for you
您可以连接两个表,但在连接时必须使用“表别名”,因为“record_num”字段在两个表中都很常见。这是对您有用的查询
select
table1.record_num,
table1.test_num,
table1.t_date,
table1.t_time,
table1.system_type,
table2.category,
table2.comments,
table2.val
from
fus_shift table1,root table2
where
table1.record_num = table2.record_num;
回答by Coentje
I would use the following method:
我会使用以下方法:
SELECT *
FROM fus_shift
INNER JOIN root ON root.record_num = fus_shift.record_num