代码之家 › 专栏 › 技术社区 › Daryn

联接表上的交集日期后缀最大日期复杂查询

postgresql sql

Daryn · 技术社区 · 6 年前

我有三张桌子,每张都有开始和结束日期。

e.g. 
Table 1
start_date | end_date | valueToJoin1 | ValueToJoin2 | fruit
 2000-01-01  2000-02-01    XXXX           YYYY       apple
 2000-01-01  2000-02-01    XXXX           BBBB       apple

Table 2
start_date | end_date | valueToJoin1 | ValueToJoin2 | vegetable
2000-01-15    2000-12-31    XXXX          YYYY        tomato

Table 3
start_date | end_date | car 
2000-01-05    2000-10-31   porsche
2000-02-02    2000-10-31   mazda

我想要一个查询,根据交叉表重叠的最大可能开始日期和重叠的最小可能结束日期列出交叉表的行。我还需要第三张桌子的开始和结束日期。

表需要由某些值连接,例如,两个表上的xxxx必须匹配。

我也不想要任何重复或不必要的信息。上面的输出应仅为一行:

 max_start_date | max_end_date | table_3_start_date | table_3_end_date |  joinedValue1 | joinedValue2 | vegetable | fruit | car
  2000-01-15       2000-02-01        2000-01-05         2000-10-31      XXXX          YYYY        tomato    apple   porsche

1 回复 | 直到 6 年前

a_horse_with_no_name 6 年前

我认为这就是您想要的-至少它根据您的示例数据提供了您想要的输出:

select greatest(tb1.start_date, tb2.start_date, tb3.start_date) as max_start_date,  
       least(tb1.end_date, tb2.end_date, tb3.end_date) as max_end_date, 
       tb3.start_date as tb3_start_date, 
       tb3.end_date as tb3_end_date,
       tb1.j1, 
       tb1.j2,
       tb1.fruit,
       tb2.vegetable,
       tb3.car
from tb1 
  join tb2 
    on (tb2.j1, tb2.j2) = (tb1.j1, tb1.j2) 
   and daterange(tb2.start_date, tb2.end_date, '[]') && daterange(tb1.start_date, tb1.end_date, '[]')
  join tb3 
    on daterange(tb3.start_date, tb3.end_date, '[]') && daterange(tb1.start_date, tb1.end_date, '[]')
   and daterange(tb3.start_date, tb3.end_date, '[]') && daterange(tb2.start_date, tb2.end_date, '[]');

在线示例: http://rextester.com/HBFY75494

推荐文章

Ishaan Adarsh · 未知的META规范,无法验证。[规范v1.0.1]

1 年前

tggtsed · PostgreSQL从平均值中排除值

1 年前

Dawid · 为什么我不能在子查询中使用表别名?

2 年前

LAZ · ORDER BY id DESC LIMIT 100返回第一行而不是最后一行

2 年前

Mohammed Shaheer · 查找列中的数据是否满足sql中的数据类型条件

2 年前

AKang123. · Postgres:重复行,但更新json中的字段

2 年前

CraZyCoDer · 在PostgreSQL中锁定潜在事务

2 年前

ranebec · 计数时如何返回0而不是null?

2 年前

Ramón J Romero y Vigil · 将没有时区列的postgres时间戳转换为日期

2 年前

Flo · 分组依据中的SQL大小写

2 年前