CREATE TABLE `transfer_order_detail` (
`tdId` int(9) unsigned NOT NULL AUTO_INCREMENT,
`barCode` varchar(9) NOT NULL,
`toTime` datetime DEFAULT NULL,
PRIMARY KEY (`tdId`)
)...
例子数据:
+-----------+-----------+---------------------+
| tdId | barCode | toTime |
+-----------+-----------+---------------------+
| 000000001 | R00100001 | 2016-04-01 15:02:35 |
| 000000002 | R00100002 | 2016-04-01 15:02:35 |
| 000000003 | R00100003 | 2016-04-01 15:02:35 |
| 000000008 | R00100001 | 2016-04-01 15:05:39 |
| 000000009 | R00100002 | 2016-04-01 15:05:39 |
| 000000010 | R00100003 | 2016-04-01 15:05:39 |
| 000000014 | R00100001 | 2016-04-03 16:49:43 |
| 000000015 | R00100002 | 2016-04-03 16:49:43 |
+-----------+-----------+---------------------+
要求以barCode分组查出每组中toTime最大的记录小于指定日期的记录.
例:小于2016-04-04 16:49:43
+-----------+-----------+---------------------+
| tdId | barCode | toTime |
+-----------+-----------+---------------------+
| 000000010 | R00100003 | 2016-04-01 15:05:39 |
| 000000014 | R00100001 | 2016-04-03 16:49:43 |
| 000000015 | R00100002 | 2016-04-03 16:49:43 |
+-----------+-----------+---------------------+
小于2016-04-01 16:49:43
+-----------+-----------+---------------------+
| tdId | barCode | toTime |
+-----------+-----------+---------------------+
| 000000010 | R00100003 | 2016-04-01 15:05:39 |
+-----------+-----------+---------------------+
2 个解决方案
#2
SELECT tdId,barCode,toTime FROM transfer_order_detail t WHERE not exists (select 1 from transfer_order_detail WHERE barCode=t.barCode and toTime>t.toTime ) AND t.toTime<'2016-04-04 16:49:43';
#1
#2
SELECT tdId,barCode,toTime FROM transfer_order_detail t WHERE not exists (select 1 from transfer_order_detail WHERE barCode=t.barCode and toTime>t.toTime ) AND t.toTime<'2016-04-04 16:49:43';