获取每个人每天的最小时间记录

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

以上是我的查询，我想获取一个人每天的记录。这个记录应该有最小的日期时间。我需要在那个日期的全部记录。

My expected output here

我的预期输出

I tried using

我试着使用

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

but for 't1' I got id=1 and entrydates of first row.

但是对于t1，我得到id=1和第一行的入口日期。

Please help me out. If duplicate then provide link.

请帮帮我。如果复制，则提供链接。

3 个解决方案

#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

DEMO

演示

#2

If you are using mysql : GROUP_CONCAT and SUBSTRING_INDEX

如果您正在使用mysql: GROUP_CONCAT和SUBSTRING_INDEX

SELECT 
  DATE(entrydates) AS grouped_date,
  GROUP_CONCAT(id ORDER BY entrydates ASC SEPARATOR ',') AS id_ordered_list,
  SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY entrydates ASC), ',', 1) AS min_id_for_day
FROM 
  accesscards 
WHERE 
  1=1 AND name!='' 
GROUP BY 
  DATE(entrydates)

If you need other fields besides id to be shown, add this to you select :

如果您需要显示除id外的其他字段，请将此添加到您所选择的:

  SUBSTRING_INDEX(GROUP_CONCAT(YOUR_FIELDNAME_HERE ORDER BY entrydates ASC), ',', 1) AS min_YOUR_FIELDNAME_for_day

Play at http://sqlfiddle.com/#!2/a2671/13

在http://sqlfiddle.com/ ! 2 / a2671/13

After you updated your question with new data: http://sqlfiddle.com/#!2/a2671/20

在您用新的数据更新您的问题之后:http://sqlfiddle.com/#!2/a2671/20

SELECT 
  DATE(entrydates) AS grouped_date,
  SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY entrydates ASC), ',', 1) AS min_id_for_day,
  department,
  name,
  SUBSTRING_INDEX(GROUP_CONCAT(entrydates ORDER BY entrydates ASC), ',', 1) AS min_entrydate_for_day
FROM 
  accesscards 
WHERE 
  1=1 AND name!='' 
GROUP BY 
  name,DATE(entrydates) 
ORDER BY entrydates

#3

Try this out this will surely help you

试试这个，这肯定对你有帮助

select id,department,name,entrydates from accesscards where entrydates in (select min(entrydates) from accesscards group by to_char(entrydates,'dd-Mon-yyyy')) order by 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