联接表时的On子句条件 [英] On clause conditions when joining tables
问题描述
我想加入两个表"Table1"和"Table2".对于表1中的每个ID，我必须获取StartTime和StopTime并引用其DateTime在表1的StartTime和StopTime之间的Table2，然后从表2中检索相应的Point 1和Point 2.在on子句中，我检查了Table1.StartTime> = Table2.DateTime和Table1.StopTime< = Table2.DateTime的位置.但是Google大查询不支持on子句中的> =.只是想知道是否对此有任何补充.任何帮助将不胜感激.
I want to join the two tables "Table1" and "Table2". For each Id in Table1, I have to get the StartTime and StopTime and refer to the Table2 whose DateTime is between the Table 1’s StartTime and StopTime and then retrieve the corresponding Point 1 and Point 2 from Table 2. I tried joining the two tables and in the on clause, I checked where Table1.StartTime >= Table2.DateTime and Table1.StopTime <= Table2.DateTime. But Google big query does not support >= in on clauses. Just wondering whether there is any supplement for this. Any help would be appreciated.
表1:
Id StartTime StopTime
1 2016-10-05 12:44:21 UTC 2016-10-05 12:59:31 UTC
表2:
Id DateTime Point1 Point2
1 2016-10-05 12:44:21 UTC 52.9 53.2
2 2016-10-05 12:44:24 UTC 33.1 90.1
3 2016-10-05 12:50:47 UTC 52.3 90.8
4 2016-10-05 12:53:23 UTC 52.3 -111.11
结果表:
Id StartTime StopTime Point1 Point2
1 2016-10-05 12:44:21 UTC 2016-10-05 12:59:31 UTC 52.9 53.2
2 2016-10-05 12:44:21 UTC 2016-10-05 12:59:31 UTC 33.1 90.1
3 2016-10-05 12:44:21 UTC 2016-10-05 12:59:31 UTC 52.3 90.8
4 2016-10-05 12:44:21 UTC 2016-10-05 12:59:31 UTC 52.3 -111.11
推荐答案
对于BigQuery标准SQL(请参阅启用标准SQL )
For BigQuery Standard SQL (see Enabling Standard SQL)
SELECT Table1.Id, StartTime, StopTime, Point1, Point2
FROM Table1 join Table2
ON Table1.StartTime <= Table2.DateTime
AND Table1.StopTime >= Table2.DateTime
或者您可以在下面的ON子句中使用
or you can use below in ON clause
ON Table2.DateTime BETWEEN Table1.StartTime AND Table1.StopTime
这篇关于联接表时的On子句条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
