Postgres Left Join with where条件 [英] Postgres Left Join with where condition

问题描述

我需要使用where条件将两个表连接起来:

I need to left join two tables with a where condition:

id     rid        start_date                end_date
1       2     2017-07-01 00:00:00     2018-11-01 00:00:00
2       5     2017-01-01 00:00:00     2017-06-01 00:00:00
3       2     2018-07-01 00:00:00     2020-11-01 00:00:00

record_table

id      name                 date
1      record1       2017-10-01 00:00:00
2      record2       2017-02-01 00:00:00
3      record3       2017-10-01 00:00:00

我需要获取在给定日期范围内存在的所有那些记录.在上面的示例中，我只需要位于rid = 2范围内的那些记录.因此，上述查询的输出需要为:

I need to get all those records which are present under given date range. In the above example, I need those records that lie under range for rid = 2 only. Hence the output for the above query needs to be:

1      record1       2017-10-01 00:00:00    
3      record3       2017-10-01 00:00:00

推荐答案

左连接两个具有where条件的表

left join two tables with a where condition

这里有一个 陷阱 在等你.使用LEFT [OUTER] JOIN时，使用WHERE条件进行过滤通常是错误，从而使LEFT JOIN的特殊功能无效，从而无条件地包含了左表中的所有行.详细说明:

There is a trap waiting for you here. When using a LEFT [OUTER] JOIN, it's typically wrong to filter with a WHERE condition, thereby voiding the special feature of a LEFT JOIN to include all rows from the left table unconditionally. Detailed explanation:

• 详细解释JOIN与LEFT JOIN和WHERE条件表现建议

因此，将要过滤所有行(rid = 2)的条件放在WHERE子句中，但要使从record_table到左连接行的条件成为实际的连接条件:

So, put conditions into the WHERE clause that are supposed to filter all rows (rid = 2), but make conditions to left-join rows from record_table out to be actual join conditions:

SELECT t.start_date, t.end_date  -- adding those
     , r.id, r.name, r.date 
FROM   time_table t
LEFT   JOIN record_table r ON r.date >= t.start_date
                          AND r.date <  t.end_date
WHERE  t.rid = 2;

如已注释，在time_table，但这是我的可选添加.

As commented, it makes sense to include columns from time_table in the result, but that's my optional addition.

您还需要非常清楚上下限.一般约定是在时间范围(timestamp)中包括下限和排除上限.因此，我在上面使用>=和<.

You also need to be very clear about upper and lower bounds. The general convention is to include lower and exclude the upper bound in time (timestamp) ranges. Hence my use of >= and < above.

相关:

• 一次执行SQL查询系列来计算平均值
• 选择记录的平均值按5分钟周期分组

• SQL query on a time series to calculate the average
• Selecting an average of records grouped by 5 minute periods

使用正确的索引，性能应该完全没问题. 在(rid)的time_table上需要一个索引(或PK)，在(date)的record_table上需要一个索引.

Performance should be no problem at all with the right indexes. You need an index (or PK) on time_table on (rid) and another on record_table on (date).

这篇关于Postgres Left Join with where条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！