在同一个表中选择两次时，BigQuery中的列名重复
在同一个表中选择两次时，BigQuery中的列名重复
提问于 2020-04-19 16:23:47
我正尝试在BigQuery中执行以下查询：
SELECT * FROM
(
SELECT
date,
grob as `grob_r1`
FROM `mytab_tbl`
WHERE region = "r1"
)
AS TAB_1,
(
SELECT
date,
grob as `grob_r2`
FROM `mytab_tbl`
WHERE region = "r2"
)
AS TAB_2
WHERE TAB_1.date = TAB_2.date它基本上是在同一个表上应用不同的条件规则进行选择，目的是创建一个视图。但是，我得到以下错误：Duplicate column names in the result are not supported. Found duplicate(s): date
你知道怎么解决这个问题吗？
Stack Overflow用户
回答已采纳
发布于 2020-04-19 16:25:50
基本上，您的SELECT *将只获取来自TAB_1和TAB_2的所有列，并且在这两种情况下，您都有date字段。
SELECT TAB_1.*, TAB_2.grob_r2 FROM
(
SELECT
date,
grob as `grob_r1`
FROM `mytab_tbl`
WHERE region = "r1"
)
AS TAB_1,
(
SELECT
date,
grob as `grob_r2`
FROM `mytab_tbl`
WHERE region = "r2"
)
AS TAB_2
WHERE TAB_1.date = TAB_2.date在您的示例中，连接条件是相等的，但是如果您希望在结果中也从TAB_2获取日期，或者如果您希望从TAB_2获取所有列，无论它们是什么，您可以执行如下操作
SELECT TAB_1.*, TAB_2.* EXCEPT(date), TAB_2.date AS tab_2_date FROM ...
编辑:帮助您解决完全加入问题。
SELECT TAB_1.*, TAB_2.grob_r2 FROM
(
SELECT
date,
grob as `grob_r1`
FROM `mytab_tbl`
WHERE region = "r1"
)
AS TAB_1 FULL JOIN
(
SELECT
date,
grob as `grob_r2`
FROM `mytab_tbl`
WHERE region = "r2"
)
AS TAB_2
ON TAB_1.date = TAB_2.date页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61301470
复制相关文章
相似问题
腾讯云开发者
