JOIN子句中的postgresql案例表达式

Question

我有2个表如下

table1

id    |      date
------+---------------------
 1    |     1/12/2017
 1    |     3/12/2017
 1    |     10/2/2018     
 1    |     10/4/2018
 2    |     1/7/2018
 2    |     12/9/2018 
 2    |     13/9/2018
 2    |     1/10/2018

table2

id    |      date1     |      date2   |  value
------+----------------+--------------+----------
 1    |     1/1/2018   |    1/1/2018  |    1
 1    |     15/2/2018  |    1/1/2018  |    4    
 1    |     10/4/2018  |    15/2/2018 |    7
 2    |     1/7/2018   |    1/7/2018  |    5
 2    |     13/9/2018  |    1/7/2018  |    2
 2    |     1/10/2018  |    13/9/2018 |    14

我想将值列添加到表1，按ID进行匹配，如果日期是日期1和日期之间。但是，在date1 = date2的情况下，条件应为日期或之前的日期1

结果应为

id    |      date       |  value
------+-----------------+---
 1    |     1/12/2017   |    1
 1    |     3/12/2017   |    1
 1    |     10/2/2018   |    4    
 1    |     10/4/2018   |    7
 2    |     1/7/2018    |    5
 2    |     12/9/2018   |    2
 2    |     13/9/2018   |    2
 2    |     1/10/2018   |    14

我尝试了以下SQL查询

SELECT table1.id, table1.date, table2.value
  FROM table1 
  LEFT JOIN table2 ON (table1.id = table2.id AND 
  CASE 
    WHEN table2.date1 = table2.date2 THEN table1.date <= table2.date
    ELSE table1.date BETWEEN table2.date2 AND table2.date1 );

和

SELECT table1.id, table1.date, table2.value
  FROM table1 
  LEFT JOIN table2 ON (table1.id = table2.id) WHERE 
  CASE 
    WHEN table2.date1 = table2.date2 THEN table1.date <= table2.date
    ELSE table1.date BETWEEN table2.date2 AND table2.date1;

但是，这并不给出我需要的结果。我在哪里犯错

Answer 1

您不需要case。只是布尔条件，这些都可以在ON子句中进行：

SELECT t1.id, t1.date, t2.value
FROM table1 t1 JOIN
     table2 t2
     ON t1.id = t2.id AND
        ((t2.date1 < t2.date2 AND
          t1.date BETWEEN t2.date1 AND t2.date2)
         ) OR
         (t2.date1 = t2.date2 AND
          t1.date <= t2.date1
         ) 
        );

您也可以简化这一点。一种方法是：

     ON t1.id = t2.id AND
        t1.date <= date2 AND
        (t2.date1 = t2.date2 OR
         t1.date >= t2.date1
        );

Answer 2

尝试以下：

SELECT table1.id, table1.date, table2.value
  FROM table1 
  JOIN table2 ON table1.id = table2.id
where  (table2.date1=table2.date2 and table1.date<=table2.date1)
or ( table1.date BETWEEN table2.date2 AND table2.date1 );

Answer 3

尝试这种方式 -

UPDATE A
SET A.Value = B.Value
FROM TABLE1 A 
INNER JOIN TABLE2 B
ON A.ID = B.ID AND A.Date BETWEEN B.Date1 AND B.DATE2

然后尝试一下 -

UPDATE A
SET A.Value = B.Value
FROM TABLE1 A 
INNER JOIN TABLE2 B
ON A.ID = B.ID AND (A.Date >= B.Date1 AND A.Date <= B.DATE2)