我写了一个SELECT,执行UNION,并在每个UNION部分使用一些JOIN。连接的表具有部分相同的列标识符。如果执行"SELECT*",ORACLE将决定显示内部列名,而不是"真实"列名。
为了显示效果,我创建了两个表(具有部分相似的列标识符"TID"one_answers"TNAME"(,并用一些数据填充它们:
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);
之后,我用以下语句选择了列:
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;
得到了这个令人困惑的结果:
QCSJ_C000000000300000 QCSJ_C000000000300002 T2ID QCSJ_C000000000300001 QCSJ_C000000000300004
1 eins 1 1 one
2 zwei 2 2 two
当语句使用表名来指定列时,一切都如我所期望的那样工作:
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
有人能解释一下吗?
我用另外两个表扩展了我的测试,以防止在语句中重复使用表:
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使用内部标识符。
根据Oracle(DocId 2658003.1(,当满足三个条件时,就会发生这种情况:
- ANSI联接
- 联合
- 同一个表在查询中出现多次
当Oracle转换ANSI样式的联接时,在内部单独使用"QCSJ_C"。
编辑:
找到一个最小的例子:
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联接语法来修复:
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
或者,最好使用列名而不是*
:
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
有趣!
然而,我永远不会将集合运算符(UNION
、UNION ALL
、INTERSECT
、MINUS
(与星号(*
(一起使用。
列的顺序可以更改,可能不是由您更改,而是由某人对数据库进行维护,或者通过使用导出/导入等将数据库迁移到新系统。简单示例:
CREATE TABLE t (a INT, b INT, c INT);
SELECT * FROM t;
A B C
ALTER TABLE t MODIFY b INVISIBLE;
ALTER TABLE t MODIFY b VISIBLE;
SELECT * FROM t;
A C B