停车场

[致命的西瓜]

数据库表

DROP TABLE IF EXISTS `ParkCarLong`;
CREATE TABLE `ParkCarLong` (
  `parkCarId` int(19) NOT NULL AUTO_INCREMENT COMMENT '编码 ',
  `carPlateNumber` varchar(20) NOT NULL COMMENT '车牌号 ',
  `parkDate` datetime NOT NULL COMMENT '进出场时间',
  `status` varchar(4) NOT NULL COMMENT '状态 进场 出场',
  PRIMARY KEY (`parkCarId`),
  KEY `Index_ParkCarLong_carPlateNumber` (`carPlateNumber`) USING BTREE,
  KEY `Index_ParkCarLong_parkDate` (`parkDate1`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=59733290 DEFAULT CHARSET=utf8;

数据
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 出场

现在要把进出场分开 并将进出场时间不是相邻两天的放到一张表里
像这样：
1 A4 2014-07-01 00:02:06 进场 2014-07-05 00:44:00 出场
2 A1 2014-07-05 01:50:00 进场 2014-07-07 02:44:00 出场


....Onz

[ACMAIN_CHM]

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

[gaofei8704]

哎呀，兄弟啊，我之前回复过你的问题了吧~~哎，又让我看到了： 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 就好了！