Get records for each person's each day's m

问题:

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.

Please help me out. If duplicate then provide link.

回答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

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 :

  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

After you updated your question with new data: 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;