获取每个人每天的最小日期时间的记录 [英] Get records for each person's each day's min datetime
本文介绍了获取每个人每天的最小日期时间的记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE TABLE IF NOT EXISTS `accesscards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`department` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`entrydates` datetime NOT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO `accesscards` (`id`, `department`, `name`, `entrydates`) VALUES
(1, 'test', 't1', '2013-12-06 16:10:00'),
(2, 'test', 't1', '2013-12-06 15:10:00'),
(3, 'test', 't1', '2013-12-07 15:11:00'),
(4, 'test', 't1', '2013-12-07 15:24:00'),
(5, 'test', 't2', '2013-12-06 16:10:00'),
(6, 'test', 't2', '2013-12-06 16:25:00'),
(7, 'test', 't2', '2013-12-07 15:59:00'),
(8, 'test', 't2', '2013-12-07 16:59:00');
以上是我的查询,我想获取一个人每天的记录.该记录应具有当天的最小日期时间.我需要该日期时间的完整记录
Above is my query, I want to get records for a person for each day. And that record should have min datetime for the day. I need whole record for that date time
我的预期输出此处
我尝试使用
SELECT id, MIN(entrydates) FROM accesscards WHERE 1=1 AND name!='' GROUP BY DATE(entrydates) ORDER BY id
但是对于't1',我的id = 1和第一行的输入日期.
but for 't1' I got id=1 and entrydates of first row.
请帮帮我.如果重复,请提供链接.
Please help me out. If duplicate then provide link.
推荐答案
SELECT a1.*
FROM accesscards a1
JOIN (SELECT name, MIN(entrydates) mindate
FROM accesscards
WHERE name != ''
GROUP BY name, date(entrydates)) a2
ON a1.name = a2.name AND a1.entrydates = a2.mindate
这篇关于获取每个人每天的最小日期时间的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文