SQL在一行中获取每个日期的最小/最大时间

Question

我需要一个帮助在这里，我试图建立一个查询，返回最大和最小时间每个日期在一行中，例如我有这个表:

+---------+------------------------+----------+
|name     |Dates                   |Door      |
+---------+------------------------+----------+
|Maria    |2012-02-14 09:04:45.397 |Garage    |
|Maria    |2012-02-14 12:14:20.997 |Entrance |
|Maria    |2013-02-14 12:20:59.407 |Exit      |
|Maria    |2012-02-13 12:24:20.997 |garage    |
|Eli      |2013-02-13 10:30:59.407 |Entrance  |
|Eli      |2013-02-13 12:30:59.407 |Exit      |
+---------+------------------------+----------+

的结果应该是:

+---------+------------------------+-----------------------------+
|name     |Entrance                |Exit                         |
+---------+------------------------+-----------------------------+
|Maria    |2012-02-14 09:04:45.397 |2013-02-14 12:20:59.407      |
|Maria    |2012-02-13 12:14:20.997 |          null               |
|Eli      |2013-02-13 10:30:59.407 |2013-02-13 12:30:59.407      |
+---------+------------------------+-----------------------------+

Answer 1

试试这个查询

    SELECT name, min(dates) as ENTRANCE, IF(max(dates)=MIN(dates),null,max(dates)) as 'Exit' 
from TEST3 GROUP BY name,day(dates);

Answer 2

我不明白结果集的第一部分。

不应该是这样吗??

+-------+---------------------+----------+
| name  | date                | door     |
+-------+---------------------+----------+
| Eli   | 2013-02-13 10:30:59 | Entrance |
| Maria | 2012-02-13 12:24:20 | Garage   |
| Maria | 2012-02-14 09:04:45 | Garage   |
+-------+---------------------+----------+