在同一个表中选择两次时，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
你知道怎么解决这个问题吗？
回答 2
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.dateStack Overflow用户
发布于 2020-04-19 20:23:07
首先，学习使用正确的JOIN语法。
然后，BigQuery允许您选择完整的记录。我通常推荐这种方法：
SELECT TAB_1, TAB_2
FROM (SELECT date, grob as grob_r1
FROM `mytab_tbl`
WHERE region = 'r1'
) TAB_1 JOIN
(SELECT date, grob as grob_r2
FROM `mytab_tbl`
WHERE region = 'r2'
) TAB_2
ON TAB_1.date = TAB_2.date;在这种情况下，您可能不需要两次DATE。BigQuery支持标准USING子句，该子句允许您执行以下操作：
SELECT *
FROM (SELECT date, grob as grob_r1
FROM `mytab_tbl`
WHERE region = 'r1'
) TAB_1 JOIN
(SELECT date, grob as grob_r2
FROM `mytab_tbl`
WHERE region = 'r2'
) TAB_2
USING (date);这将返回三列。
另一个方便的替代方案是EXCEPT，在本例中并不需要它
SELECT TAB_1.*,
TAB_2.* EXCEPT (date)
FROM (SELECT date, grob as grob_r1
FROM `mytab_tbl`
WHERE region = 'r1'
) TAB_1 JOIN
(SELECT date, grob as grob_r2
FROM `mytab_tbl`
WHERE region = 'r2'
) TAB_2
ON TAB_1.date = TAB_2.date;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61301470
复制相关文章
相似问题
腾讯云开发者
