在雅典娜中创建视图，但"column name specified more than once"

Question

我创建了一个SELECT查询，连接AWS Athena中的三个表-查询本身工作…

Select t1.*, t2.*, t3.*
from "analytics_poc"."stg_orderitem" as t1
INNER Join "analytics_poc"."stg_orderitemtag" as t2
ON t1.orderitemid=t2.orderitemid
LEFT Join "analytics_poc"."stg_tag" as t3
ON t3.tagid=t2.tagid

然而，当我试图从这个查询创建一个视图时，我得到这个错误…

CREATE OR REPLACE VIEW "CMS_orderitem_tags"
AS 
Select t1.*, t2.*, t3.*
from "analytics_poc"."stg_orderitem" as t1
INNER Join "analytics_poc"."stg_orderitemtag" as t2
ON t1.orderitemid=t2.orderitemid
LEFT Join "analytics_poc"."stg_tag" as t3
ON t3.tagid=t2.tagid

line 1:1: Column name 'orderitemid' specified more than once.

有人知道为什么会这样吗?

Answer 1

似乎一个名为orderitemid的列存在于不止一个这样的表中。

SELECT命令不介意有多个相同名称的输出列，但CREATE VIEW不允许。

您可以尝试USING，它删除了'join'列。

改变这一行:

INNER Join "analytics_poc"."stg_orderitemtag" as t2 ON t1.orderitemid=t2.orderitemid

为:

INNER Join "analytics_poc"."stg_orderitemtag" as t2 ON USING(orderitemid)