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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 17:32:34  来源:igfitidea点击:

combining and joining two tables with different no. of columns and same column name

sqloraclesqlplusora-00918

提问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