WITH 子句子查询具有非唯一的列名

Question

我正在使用 Oracle11g，并希望使用 WITH 子句创建一个命名的子查询，以便我可以在多个地方引用"子查询"结果。我面临的问题在以下虚拟表格的帮助下被模仿了。

CREATE TABLE table1(id integer, region varchar2(20), xfunction varchar2(20), entity varchar2(20), xtime varchar2(20), usd binary_double);
insert into table1 values(1,'region1','function1','entity1','2018-01',1);
insert into table1 values(2,'region2','function2','entity2','2018-02',2);
CREATE TABLE table2(id integer, product varchar2(20), market varchar2(20));
insert into table2 values(1,'product1','market1');
insert into table2 values(2,'product2','market2');
CREATE TABLE table3(id integer, geo varchar2(20));
insert into table3 values(1,'geo1');
insert into table3 values(2,'geo2');

以下查询给出错误"ORA-00904："V1"。ID"： 无效标识符"：

with v1 as
(
select
* -- want to select all columns, this is intentional
from
table1 a
left join table2 b ON 
a.id = b.id
),
v2 as
(
select * from v1
join table3 c on
v1.id = c.id -- gives error--> ORA-00904: "V1"."ID": invalid identifier
order by
c.id
)
select * from v2;

v1 有两列具有相同的列名(即"ID"(。如何从 v1 引用表 1 的 ID 列(当表 2 的 ID 列也存在于 v1 中时(？

我知道我可以重写上述查询，而无需使用 WITH 子句作为解决方法或在 v1 中仅选择一个 ID 列。但我真正想要的是能够使用 WITH 子句(在 v1 中有两个 ID 列(并使其工作。可能吗？

Answer 1

Oracle 支持using子句，它完全符合您的要求：

with v1 as (
select *
from table1 a left join
table2 b 
using (id)
),

一般来说，我更喜欢明确列出列——至少对于其中一个表。 但是，我也认识到能够表达这一点的便利性。

注意：仅当id是两个表中唯一的重复列时，此操作才有效。

Answer 2

在您的情况下，问题出在 v1 中加入 -

a.id= b.id

表 1 中有一个名为 id1 的列，而不是 id。 使用 id1 运行相同的查询，我的输出为 -

ID1,REGION,XFUNCTION,ENTITY,XTIME,USD,ID,PRODUCT,MARKET,ID_1,GEO
1,region1,function1,entity1,2018-01,1.0,1,product1,market1,1,geo1
2,region2,function2,entity2,2018-02,2.0,2,product2,market2,2,geo2

查询-

with v1 as
(
select
* -- want to select all columns, this is intentional
from
table1 a
left join table2 b ON 
a.id1 = b.id
),
v2 as
(
select * from v1
join table3 c on
v1.id = c.id -- gives error--> ORA-00904: "V1"."ID": invalid identifier
order by
c.id
)
select * from v2;