mysql - 上期SQL选择

Question

我有一个这样的表:

<table border="1" cellspacing="0" cellpadding="5">
<tr><td>ID</td><td>status</td><td>start_date</td><td>end_date</td></tr>
<tr><td>1</td><td>A</td><td>2015-01-01</td><td>2015-12-31</td></tr>
<tr><td>1</td><td>B</td><td>2016-01-01</td><td>NULL</td></tr>
<tr><td>2</td><td>C</td><td>NULL</td><td>2016-12-31</td></tr>
<tr><td>3</td><td>D</td><td>NULL</td><td>NULL</td></tr>
</table>

我必须在 MySQL 中执行触发器来更新主表，其中包含主题的最后状态。

为此，我必须根据 start_date 和 end_date 之间的时间段为每个 ID 选择最后一个 status > 如果不知道的话可以为 NULL。

我写这个SQL语句测试:

SELECT *
FROM My_Table AS T
WHERE T.start_date Is Null AND
T.end_date In(SELECT MAX(end_date) FROM My_Table WHERE ID = T.ID)
OR
T.start_date In(SELECT MAX(start_date) FROM My_Table WHERE ID = T.ID) AND 
T.end_date Is Null
OR
T.start_date In(SELECT MAX(start_date) FROM My_Table WHERE ID = T.ID) AND
T.end_date In(SELECT MAX(end_date) FROM My_Table WHERE ID = T.ID
);

有没有一种缩短方法可以做到这一点？

实际上，我想要一个 WHERE 子句来捕获每种情况(a = start_date，b=end_date):

a = 最大值与 b = 最大值或
a = NULL AND b = 最大或
a = 最大值且 b = NULL 或
a = NULL AND b = NULL

Answer 1

这是一个稍微简化的查询，它返回与您问题中的查询完全相同的结果集。它仅使用两个子查询而不是四个(没有重复的子查询):

SELECT *
FROM My_Table AS T
WHERE 
  (T.start_date Is Null OR T.start_date = (SELECT MAX(start_date) FROM My_Table WHERE ID = T.ID)) AND
  (T.end_date Is Null OR T.end_date = (SELECT MAX(end_date) FROM My_Table WHERE ID = T.ID)) AND 
  (T.start_date Is NOT Null OR T.end_date Is NOT Null)
;

请注意，它不包括 a = NULL AND b = NULL 情况。要包含它，必须删除最后一个 AND 子句，即:

SELECT *
FROM My_Table AS T
WHERE 
  (T.start_date Is Null OR T.start_date = (SELECT MAX(start_date) FROM My_Table WHERE ID = T.ID)) AND 
  (T.end_date Is Null OR T.end_date = (SELECT MAX(end_date) FROM My_Table WHERE ID = T.ID)) 
;