MySQL：获取日期的min和mx

Question

我需要激发一个查询来检索from_date的最小值和to_date的最大值。我的数据如下：

+--------------------------------------+--------------------------------------+---------------------+---------------------+
| user_id                              | point_id                             |         from_date   | to_date             |
+--------------------------------------+--------------------------------------+---------------------+---------------------+
| 1e159ee9-0856-4ea8-88ab-4c0862f667af | c54c3bea-46e8-4377-bfa0-3a03c7abbafa | 2007-07-31 00:00:00 | 2012-04-08 00:00:00 |
| 1e159ee9-0856-4ea8-88ab-4c0862f667af | c54c3bea-46e8-4377-bfa0-3a03c7abbafa | 2012-04-09 00:00:00 | NULL                |
+--------------------------------------+--------------------------------------+---------------------+---------------------+

但是，当我激发以下查询时：

select min(from_date), max(to_date) from some_table where user_id='1e159ee9-0856-4ea8-88ab-4c0862f667af' and point_id ='c54c3bea-46e8-4377-bfa0-3a03c7abbafa' order by from_date;
+------------------------+----------------------+
| min(from_date)         | max(to_date) |
+------------------------+----------------------+
| 2007-07-31 00:00:00    | 2012-04-08 00:00:00  |
+------------------------+----------------------+

如您所见，我希望结果中的to_date为NULL。但Max函数不会发现我为空。那么我有什么替代方法使我返回空值呢？此外，to_date列可能不是NULL。在这种情况下，我需要非空的适当日期。谁能帮帮忙吗？

Answer 1

SQL小提琴

MySQL 5.6架构设置：

CREATE TABLE some_table (
  user_id varchar(64),
  point_id varchar(64),
  from_date date,
  to_date date  
  );


  INSERT INTO some_table VALUES
  ('1e159ee9-0856-4ea8-88ab-4c0862f667af', 'c54c3bea-46e8-4377-bfa0-3a03c7abbafa', '2007-07-31 00:00:00', '2012-04-08 00:00:00'),
  ('1e159ee9-0856-4ea8-88ab-4c0862f667af', 'c54c3bea-46e8-4377-bfa0-3a03c7abbafa', '2012-04-09 00:00:00', NULL);

查询1：

select min(from_date),
 case when MAX(to_date IS NULL) = 0 THEN max(to_date) END AS  max_to_date
from some_table
where user_id='1e159ee9-0856-4ea8-88ab-4c0862f667af' 
  and point_id ='c54c3bea-46e8-4377-bfa0-3a03c7abbafa'

结果：

|         min(from_date) | max_to_date |
|------------------------|-------------|
| July, 31 2007 00:00:00 |      (null) |

一些解释：

1-(to_date为NULL)返回1或0

2-MAX(to_date IS NULL)如果存在则取空值

3-MAX(NULL)=NULL，如果不是这种情况，您应该在您的case/when中添加一个ELSE