我需要一些帮助，我在其他帖子中寻找解决方案，但我无法使这些解决方案工作。
我有一个包含一堆id的表…
table1
customerId | adsId | campaignId | contextId | Date | amoutDue
------------------------------------------------------------------------
1234341233 | 4r4w3 | cp123sifs | cbgvss23r23dd | 03-09-2021 | 50
4556666666 | 35533 | cp453f44f | cb2d3d23dd33d | 04-06-2021 | 25
3444468766 | 12345 | cpef4f44r | ccascasca333s | 23-04-2021 | 13
6346522452 | qw324 | cpfe4r333 | c23ddsasssd2f | 12-07-2021 | 71
3463466346 | 23423 | cp1vt5rfr | kjhcbwiytcubp | 25-02-2021 | 41
4534634346 | 534f3 | cp4343ff4 | apisuchha9shh | 14-05-2021 | 36
7743646346 | wer32 | cp4ffwefe | cq98hd98ladse | 12-03-2021 | 53
2424233335 | r4w4r | cpwerewff | q09jucidnionc | 05-06-2021 | 65
在另一个表中我有adsId和adsName:
table2(adsId/adsName)
adsName | adsId
--------------------
ad-google1 | 4r4w3
ad-fbtests | 35533
ad-bingpro | 12345
ad-maillan | qw324
ad-cp-e312 | 23423
ad-gads2-4 | 534f3
ad-adProsk | wer32
ad-cpcB151 | r4w4r
在第三张表上我有campaingName和campaingId:
table3(campaingId/campaignName)
campaignName | campaignId
------------------------
cp-adWords | cp123sifs
cp-looki12 | cp453f44f
cp-cvccp53 | cpef4f44r
cp-Pliiltr | cpfe4r333
cp-Yellinf | cp1vt5rfr
cp-Iuliucs | cp4343ff4
cp-Ieventx | cp4ffwefe
cp-Shoofab | cpwerewff
最后第4个表我有contextId和contextName:
table4(contextId/contextName)
contextId | contextName
-----------------------------
cbgvss23r23dd | ctx-Okkels
cb2d3d23dd33d | ctx-Yumeis
ccascasca333s | ctx-Niehok
c23ddsasssd2f | ctx-Sluiru
kjhcbwiytcubp | ctx-Strogi
apisuchha9shh | ctx-Slucha
cq98hd98ladse | ctx-Epuent
q09jucidnionc | ctx-Igeegs
我要找的是第5个表，从其他表(包含id的)中获取所有名称，并给出名称:
table5
customerId | adsId | campaignId | contextId | Date | amoutDue | adsName |campaignName| contextName
---------------------------------------------------------------------------------------------------------------------
1234341233 | 4r4w3 | cp123sifs | cbgvss23r23dd | 03-09-2021 | 50 | ad-google1 | cp-adWords | ctx-Okkels
4556666666 | 35533 | cp453f44f | cb2d3d23dd33d | 04-06-2021 | 25 | ad-fbtests | cp-looki12 | ctx-Yumeis
3444468766 | 12345 | cpef4f44r | ccascasca333s | 23-04-2021 | 13 | ad-bingpro | cp-cvccp53 | ctx-Niehok
6346522452 | qw324 | cpfe4r333 | c23ddsasssd2f | 12-07-2021 | 71 | ad-maillan | cp-Pliiltr | ctx-Sluiru
3463466346 | 23423 | cp1vt5rfr | kjhcbwiytcubp | 25-02-2021 | 41 | ad-cp-e312 | cp-Yellinf | ctx-Strogi
4534634346 | 534f3 | cp4343ff4 | apisuchha9shh | 14-05-2021 | 36 | ad-gads2-4 | cp-Iuliucs | ctx-Slucha
7743646346 | wer32 | cp4ffwefe | cq98hd98ladse | 12-03-2021 | 53 | ad-adProsk | cp-Ieventx | ctx-Epuent
2424233335 | r4w4r | cpwerewff | q09jucidnionc | 05-06-2021 | 65 | ad-cpcB151 | cp-Shoofab | ctx-Igeegs
我尝试使用join，但我不能选择所有的表…我的SQL语句是:
get campaingName from table 2 and create a row with the name where campaingId from table1 = to campaingId from table 2所有字段相同…
提前感谢!
您可以这样使用join
select *
from table1 t1 join
table2 t2
using (adsid) join
table3 t3
using (campaignid) join
table4 t4
using (contextid);
上面的工作是因为join键在引用表和原始表中具有相同的名称(最佳实践)。您还可以使用更详细的:
select t1.*, t2.adsName, t3.campaignName, t4.contextName
from table1 t1 join
table2 t2
on t2.adsid = t1.adsid join
table3 t3
on t3.campaignid = t1.campaignid join
table4 t4
on t4.contextid = t1.contextid;
如果缺少一些id，则使用left join代替join。
您可以在大查询中使用多个with子句来实现这一点。
WITH TEST1 AS
(select a1.*,a2.adsName
from `<project_name.<dataset_name>.table1` a1, `<project_name.<dataset_name>.table2` a2
where a1.adsId=a2.adsId),
TEST2 as
(
select TEST1.*,a3.campaignName
from TEST1, `<project_name.<dataset_name>.table3` a3
WHERE TEST1.campaignId=a3.campaignId)
select TEST2.*, a4.contextName
from TEST2,`<project_name.<dataset_name>.table4` a4
WHERE TEST2.contextId=a4.contextId