停车场

Question

为什么结果中不需要 A1 2014-07-01 00:00:41 进场记录？
建议描述清楚逻辑。

Answer 1

哎呀，兄弟啊，我之前回复过你的问题了吧~~哎，又让我看到了：
1、这个是相差时间大于24小时的：mysql> SELECT * FROM ParkCarLong a ;
+-----------+----------------+---------------------+--------+
| parkCarId | carPlateNumber | parkDate            | status |
+-----------+----------------+---------------------+--------+
|         1 | A1             | 2014-07-01 00:00:41 | 进场   |
|         2 | A2             | 2014-07-01 00:00:42 | 进场   |
|         3 | A2             | 2014-07-01 00:22:55 | 出场   |
|         4 | A3             | 2014-07-01 00:00:58 | 进场   |
|         5 | A4             | 2014-07-01 00:02:06 | 进场   |
|         6 | A1             | 2014-07-01 01:00:23 | 出场   |
|         7 | A3             | 2014-07-02 00:22:42 | 出场   |
|         8 | A4             | 2014-07-05 00:44:00 | 出场   |
|         9 | A1             | 2014-07-05 01:50:00 | 进场   |
|        10 | A1             | 2014-07-07 02:44:00 | 出场   |
+-----------+----------------+---------------------+--------+
10 rows in set (0.00 sec)mysql> SELECT *  FROM
    -> (
    -> SELECT a.carPlateNumber,a.parkDate intime ,MIN(b.parkDate)   outtime
    -> FROM
    -> (SELECT * FROM ParkCarLong a WHERE a.status='进场') a
    -> LEFT JOIN
    -> (SELECT * FROM ParkCarLong a WHERE a.status='出场') b
    -> ON a.carPlateNumber=b.carPlateNumber
    -> AND a.parkDate<b.parkDate
    -> GROUP BY a.carPlateNumber,a.parkDate
    -> ) t
    -> WHERE   SUBSTR(TIMEDIFF(t.outtime,t.intime),1,2)>=24;
+----------------+---------------------+---------------------+
| carPlateNumber | intime              | outtime             |
+----------------+---------------------+---------------------+
| A1             | 2014-07-05 01:50:00 | 2014-07-07 02:44:00 |
| A3             | 2014-07-01 00:00:58 | 2014-07-02 00:22:42 |
| A4             | 2014-07-01 00:02:06 | 2014-07-05 00:44:00 |
+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)
你描述的进出场时间不是相邻两天的过滤条件把
SUBSTR(TIMEDIFF(t.outtime,t.intime),1,2)>=24;
换成
 DATEDIFF(t.outtime,t.intime) >1就好了！