ORACLE 12.2.01从具有相似名称的不同表中选择列->使用的内部列标识符 [英] ORACLE 12.2.01 selecting columns from different tables with similar names --> internal column identifier used

问题描述

我编写了一个执行UNION的SELECT，并在每个UNION部分中使用了一些JOIN.连接的表具有部分相同的列标识符.如果执行"SELECT *"，ORACLE决定显示内部列名，而不是真实"列名.

I wrote a SELECT performing a UNION and in each UNION part using some JOINs. The tables, which are joined have partly the same column identifiers. And if a "SELECT *" is performed, ORACLE decides to display the internal column names instead of the "real" column names.

为了显示效果，我创建了两个表(具有部分相似的列标识符"TID"和"TNAME")，并用一些数据填充了它们:

To show the effect I created two tables (with partly similar column identifiers, "TID" and "TNAME") and filled them with some data:

create table table_one (tid number(10), tname varchar2(10), t2id number(10));
create table table_two (tid number(10), tname varchar2(10));

insert into table_two values (1,'one');
insert into table_two values (2,'two');
insert into table_two values (3,'three');

insert into table_one values (1,'eins',1);
insert into table_one values (2,'zwei',2);
insert into table_one values (3,'drei',3);

我随后使用以下语句选择了列:

The I SELECTED the columns afterwards with the following statement:

select * 
from table_one 
inner join table_two on table_two.tid = table_one.t2id
where table_one.tid = 1
union
select * 
from table_one 
inner join table_two on table_two.tid = table_one.t2id
where table_one.tid = 2;

得到了这个令人困惑的结果:

And got this confusing result:

QCSJ_C000000000300000 QCSJ_C000000000300002       T2ID QCSJ_C000000000300001 QCSJ_C000000000300004
                    1 eins                           1                     1 one
                    2 zwei                           2                     2 two

当用表名编写语句以指定列时，一切都会按我的预期进行:

When the statement is written with tablenames to specify the columns, everything works as I expected:

select table_one.* , table_two.*
from table_one 
inner join table_two on table_two.tid = table_one.t2id
where table_one.tid = 1
minus
select * 
from table_one 
inner join table_two on table_two.tid = table_one.t2id
where table_one.tid = 2;

   TID TNAME            T2ID        TID TNAME
     1 eins                1          1 one
     2 zwei                2          2 two

有人可以解释吗?

我在测试中增加了两个表，以防止语句中表的重复使用:

I expanded my tests with two more tables to prevent double usage of table in the statement:

create table table_3 (tid number(10), tname varchar2(10), t4id number(10));
create table table_4 (tid number(10), tname varchar2(10));

insert into table_4 values (1,'one');
insert into table_4 values (2,'two');
insert into table_4 values (3,'three');

insert into table_3 values (1,'eins',1);
insert into table_3 values (2,'zwei',2);
insert into table_3 values (3,'drei',3);

select * 
from table_one 
inner join table_two on table_two.tid = table_one.t2id
where table_one.tid = 1
union
select * 
from table_3
inner join table_4 on table_4.tid = table_3.t4id
where table_3.tid = 2;

select * 
from table_one 
inner join table_two on table_two.tid = table_one.t2id
where table_one.tid = 1
union
select * 
from table_3
inner join table_4 on table_4.tid = table_3.t4id
where table_3.tid = 2;

结果是相同的.Oracle使用内部标识符.

The result is the same. Oracle uses internal identifiers.

推荐答案

根据Oracle(DocId 2658003.1)，当满足三个条件时会发生这种情况:

According to Oracle (DocId 2658003.1), this happens when three conditions are met:

1. ANSI连接
2. UNION/UNION ALL
3. 同一张表在查询中出现多次

显然，当Oracle转换ANSI样式联接时，在内部使用"QCSJ_C".

Aparently, "QCSJ_C" is used internally when Oracle transforms ANSI style joins.

找到一个最小的例子:

SELECT * FROM dual d1 JOIN dual d2 ON d1.dummy=d2.dummy
UNION
SELECT * FROM dual d1 JOIN dual d2 ON d1.dummy=d2.dummy;

QCSJ_C000000000300000 QCSJ_C000000000300001
X                     X

它可以通过使用非ANSI连接语法来解决:

It can be fixed by either using non-ANSI join syntax:

SELECT * FROM dual d1, dual d2 WHERE d1.dummy=d2.dummy
UNION
SELECT * FROM dual d1, dual d2 WHERE d1.dummy=d2.dummy;

DUMMY DUMMY_1
X     X

或者，最好使用列名代替 * :

Or, preferably by using column names instead of *:

SELECT d1.dummy, d2.dummy FROM dual d1 JOIN dual d2 ON d1.dummy=d2.dummy
UNION
SELECT d1.dummy, d2.dummy FROM dual d1 JOIN dual d2 ON d1.dummy=d2.dummy;

DUMMY DUMMY_1
X     X

这篇关于ORACLE 12.2.01从具有相似名称的不同表中选择列->使用的内部列标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！