gpt4 book ai didi

sql - 我想在大查询中连接两个具有公共(public)列的表?

转载 作者:行者123 更新时间:2023-12-02 15:03:28 25 4
gpt4 key购买 nike

为了连接表,我使用了以下查询。

SELECT *
FROM(select user as uservalue1 FROM [projectname.FullData_Edited]) as FullData_Edited
JOIN (select user as uservalue2 FROM [projectname.InstallDate]) as InstallDate
ON FullData_Edited.uservalue1=InstallDate.uservalue2;

查询有效,但连接表只有两列 uservalue1 和 uservalue2。我想保留两个表中的所有列。知道如何实现吗?

最佳答案

#legacySQL
SELECT <list of fields to output>
FROM [projectname:datasetname.FullData_Edited] AS FullData_Edited
JOIN [projectname:datasetname.InstallDate] AS InstallDate
ON FullData_Edited.user = InstallDate.user

或(更可取)

#standardSQL
SELECT <list of fields to output>
FROM `projectname.datasetname.FullData_Edited` AS FullData_Edited
JOIN `projectname.datasetname.InstallDate` AS InstallDate
ON FullData_Edited.user = InstallDate.user

请注意,在这种情况下使用 SELECT * 会导致 Ambiguous column name 错误,因此最好将您需要的列/字段的明确列表放入您的输出

解决方法是使用 USING()语法如下例所示。
假设 user 是唯一不明确的字段 - 它可以解决问题

#standardSQL
SELECT *
FROM `projectname.datasetname.FullData_Edited` AS FullData_Edited
JOIN `projectname.datasetname.InstallDate` AS InstallDate
USING (user)

例如:

#standardSQL
WITH `projectname.datasetname.FullData_Edited` AS (
SELECT 1 user, 'a' field1
),
`projectname.datasetname.InstallDate` AS (
SELECT 1 user, 'b' field2
)
SELECT *
FROM `projectname.datasetname.FullData_Edited` AS FullData_Edited
JOIN `projectname.datasetname.InstallDate` AS InstallDate
USING (user)

返回

user    field1  field2   
1 a b

而使用 ON FullData_Edited.user = InstallDate.user 给出以下错误

错误:不支持结果中的重复列名。找到重复项:用户

关于sql - 我想在大查询中连接两个具有公共(public)列的表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48175579/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com