mysql 求最小时间记录,mysql-获取每个人每天的最小日期时间的记录

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');

上面是我的查询,我想获取一个人每天的记录.该记录应具有当天的最小日期时间.我需要该日期时间的完整记录

我的预期输出here

我尝试使用

SELECT id, MIN(entrydates) FROM accesscards WHERE 1=1 AND name!='' GROUP BY DATE(entrydates) ORDER BY id

但是对于“ t1”,我得到id = 1和第一行的输入日期.

请帮帮我.如果重复,则提供链接.

解决方法:

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

标签：greatest-n-per-group,sql,mysql

来源： https://codeday.me/bug/20191122/2058087.html