google-bigquery - 尝试合并 2 个包含联接操作的子查询时，BigQuery 联合失败

Question

我们正在尝试合并 2 个子查询并收到错误。这是查询:

select *
from
(select  TRANS_SEQ, TRANS_TS
from [X.ls_transaction] ls
left join each (select * from [X.resident_s] where date(eff_end_ts) = date('9999-12-31')) rs on ls.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) a,
(select TRANS_SEQ, TRANS_TS,
from [X.ld_transaction] ld
left join each (select * from [X.resident_d] where date(eff_end_ts) = date('9999-12-31')) rs on ld.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) b
limit 1;

这是它返回的错误:

Error: Union results in ambiguous schema. [TRANS_SEQ] is ambiguous and is aliasing multiple fields. Aliased fields: ld.TRANS_SEQ,ls.TRANS_SEQ,

如果我从子查询中取出两个连接，那么联合就可以工作。
这是一个错误吗？

谢谢

Answer 1

很可能您在 X.resident_d 和 X.resident_s 中有 TRANS_SEQ 字段，这会引入歧义。
你应该做如下的事情

select *
from
(select ls.TRANS_SEQ as TRANS_SEQ, TRANS_TS
from [X.ls_transaction] ls
left join each (select * from [X.resident_s] where date(eff_end_ts) = date('9999-12-31')) rs on ls.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) a,
(select ld.TRANS_SEQ as TRANS_SEQ, TRANS_TS,
from [X.ld_transaction] ld
left join each (select * from [X.resident_d] where date(eff_end_ts) = date('9999-12-31')) rs on ld.source_resident_key=rs.resident_key
where date(trans_ts) = date('2015-07-31')) b
limit 1;