mysql - 基于时间戳的Where条件

Question

我想从表中选择 MEMBER_STATUS = 2 的所有条目。但是，此状态可能会随着时间而改变。每次成员状态更改时，旧条目都会保存在另一个名为 ADRESS_history 的表中。

ADDRESS (each ID only exists once)
ID | FIRSTNAME | LASTNAME | MEMBER_STATUS | DTM
1  | Tom       | Mueller  | 2             | 2016-04-02
2  | John      | Doe      | 2             | 2016-03-02
3  | David     | Allen    | 4             | 2016-04-04
4  | Neil      | Fiore    | 8             | 2016-04-04

ADDRESS_history (IDs can have multiple entries)
ID | FIRSTNAME | LASTNAME | MEMBER_STATUS | DTM
2  | John      | Doe      | 2             | 2012-02-02
3  | David     | Allen    | 2             | 2013-03-01
1  | Tom       | Mueller  | 1             | 2015-03-30
2  | John      | Doe      | 2             | 2015-08-22
1  | Tom       | Mueller  | 4             | 2012-02-02
1  | Tom       | Mueller  | 3             | 2013-02-02

当 ADDRESS 表中的最后一次更改发生在 2016 年 4 月 1 日之后时，我想使用 TABLE ADDRESS_history 中用户最新条目的成员状态。当 ADDRESS 表中的最后一次更改发生在 2016 年 4 月 1 日之前时，我想使用 ADDRESS 表中条目的成员状态。如何更改此查询以反射(reflect)这一点？

select *
from ADDRESS a
join ADDRESS_history using (ID)
MEMBER_STATUS = 2 

结果应该是

ID | FIRSTNAME | LASTNAME | MEMBER_STATUS | DTM
1  | Tom       | Mueller  | 1             | 2015-03-30
2  | John      | Doe      | 2             | 2016-03-02
3  | David     | Allen    | 2             | 2013-03-01

Answer 1

您可以从这里开始。

SELECT a.id
     , a.firstname
     , a.lastname
     , if( a.dtm >= '2016-04-01'
         , ( SELECT ah.member_status
               FROM address_history ah
               WHERE ah.id = a.id
                 AND ah.dtm = t.dtm
           )
         , a.member_status
         ) member_status
     , if( a.dtm >= '2016-04-01'
         , t.dtm
         , a.dtm
         ) dtm
  FROM address a
    LEFT JOIN ( SELECT id, max(dtm) dtm
                  FROM address_history
                  WHERE dtm < '2016-04-01'
                  GROUP BY id
              ) t
      ON t.id = a.id
  WHERE a.member_status = 2
    OR t.id is not null