Google BigQuery SQL使用JOIN操作从3个表中获取数据。

huangapple go评论55阅读模式
英文:

Google BigQuery SQL using a JOIN to bring back data from 3 tables?

问题

需要在BigQuery中对SQL进行一些帮助,我有以下3个表格:

表格1

表格名称 = 'Store_Table'
ItemNumber
StoreNo
StoreAddress
StorePhoneNumber
StoreType
StoreOwner

表格2

表格名称 = 'Item_Table'
ItemNumber
ItemWeight

表格3

表格名称 = 'Description_Table'
ItemNumber
ItemColor

我想从表格1中获取所有数据,以及从表格2获取ItemWeight和从表格3获取ItemColor。共同字段是ItemNumber,所以我认为我可以使用它来连接(我假设是“左外连接”?)数据,但如何连接数据以从三个表格中获取字段,并且只让ItemNumber在结果中出现一次呢?

还有一个其他条件,就是必须包含一个where子句,例如:

where StoreType = 'city' and StoreOwner not in ('active','trial')。

有什么建议吗?谢谢。

对于只涉及两个表格的情况,您可以尝试以下查询:

SELECT
Table1.ItemNumber AS ItemNumber,
Table1.StoreNo  AS StoreNo,
Table1.StoreAddress  AS StoreAddress,
Table1.StorePhoneNumber  AS StorePhoneNumber,
Table1.StoreType  AS StoreType,
Table1.StoreOwner  AS StoreOwner,
Table2.ItemWeight  AS ItemWeight
FROM `Store_Table` AS Table1
LEFT JOIN `Item_Table` AS Table2
ON Table1.ItemNumber = Table2.ItemNumber
WHERE Table1.StoreType = 'city' and Table1.StoreOwner not in ('active','trial');

这将从'Store_Table'和'Item_Table'中获取数据,通过ItemNumber字段连接它们,然后应用您的筛选条件。如果您还需要从'Description_Table'获取数据,您可以使用类似的方法,将其连接到上述查询中。

英文:

needed a bit of help with SQL in BigQuery, I have the 3 tables below:

Table1

*Table Name = ‘Store_Table’*
ItemNumber
StoreNo
StoreAddress
StorePhoneNumber
StoreType
StoreOwner

Table2

*Table Name = ‘Item_Table’*
ItemNumber
ItemWeight

Table3

*Table Name = ‘Description_Table’*
ItemNumber
ItemColor

I want to bring back all the data from table 1 plus ItemWeight from table 2 and ItemColor from table 3. The common field is ItemNumber so I presume I use that to JOIN (I presume a ‘left outer join’?) the data but how do I join the data to bring back fields from the three tables plus only have ItemNumber appear once in the result?

There is also one other criteria, which is it has to have a where clause included, example:

where StoreType = 'city' and StoreOwner not in ('active','trial').

Any ideas? Thanks.

Tried the below for just 2 tables:

SELECT
Table1.gpid AS field1,
Table1.ItemNumber AS field2,
Table1.StoreNo  AS field3,
Table1.StoreAddress  AS field4,
Table1.StorePhoneNumber  AS field5,
Table1.StoreType  AS field6,
Table1.StoreOwner  AS field7,
Table2.ItemNumber AS field8,
Table2.ItemWeight  AS field9,
FROM `Store_Table` Table1
LEFT OUTTER JOIN `Item_Table` Table2
ON Table1.ItemNumber = Table2.ItemNumber
StoreType = 'city' and StoreOwner not in ('active','trial')

答案1

得分: 0

以下是翻译好的代码部分:

SELECT *
FROM Store_Table AS S
LEFT OUTER JOIN Item_Table AS I
ON S.ItemNumber = I.ItemNumber
LEFT OUTER JOIN Description AS D
ON I.ItemNumber = D.ItemNumber
WHERE S.StoreType = 'city'
AND S.StoreOwner not in ('active','trial');
英文:

You had a few syntax errors in your query but I'm guessing you want something like this:

SELECT *
FROM Store_Table AS S
LEFT OUTER JOIN Item_Table AS I
ON S.ItemNumber = I.ItemNumber
LEFT OUTER JOIN Description AS D
ON I.ItemNumber = D.ItemNumber
WHERE S.StoreType = 'city'
AND S.StoreOwner not in ('active','trial');

huangapple
  • 本文由 发表于 2023年2月6日 06:54:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75356059.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定