MySQL order by before group by DAY(timestamp)

Question

我有下表 uc_likes

CREATE TABLE `uc_likes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(255) NOT NULL,
  `dwable` int(255) NOT NULL,
  `actionby` int(255) NOT NULL,
  `actionto` int(255) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=257 ;

INSERT INTO `uc_likes` (`id`, `type`, `dwable`, `actionby`, `actionto`, `time`) VALUES
(38, 1, 309, 4, 1, '2016-09-12 13:04:01'),
(41, 1, 372, 4, 1, '2016-09-15 13:04:07'),
(42, 1, 373, 4, 1, '2016-09-10 13:04:08'),
(55, 1, 416, 4, 1, '2016-09-15 13:04:09'),
(253, 1, 307, 5, 1, '2016-09-15 13:04:12'),
(256, 1, 372, 5, 1, '2016-09-15 13:04:13');

SELECT * FROM uc_likes;
+-----+------+--------+----------+----------+---------------------+
| id  | type | dwable | actionby | actionto | time                |
+-----+------+--------+----------+----------+---------------------+
|  38 |    1 |    309 |        4 |        1 | 2016-09-12 13:04:01 |
|  41 |    1 |    372 |        4 |        1 | 2016-09-15 13:04:07 |
|  42 |    1 |    373 |        4 |        1 | 2016-09-10 13:04:08 |
|  55 |    1 |    416 |        4 |        1 | 2016-09-15 13:04:09 |
| 253 |    1 |    307 |        5 |        1 | 2016-09-15 13:04:12 |
| 256 |    1 |    372 |        5 |        1 | 2016-09-15 13:04:13 |
+-----+------+--------+----------+----------+---------------------+

http://sqlfiddle.com/#!9/62794a

我正在尝试获取

+-----+------+--------+----------+----------+---------------------+
| id  | type | dwable | actionby | actionto | time                |
+-----+------+--------+----------+----------+---------------------+
| 256 |    1 |    372 |        5 |        1 | 2016-09-15 13:04:13 |
| 253 |    1 |    307 |        5 |        1 | 2016-09-15 13:04:12 |  
|  55 |    1 |    416 |        4 |        1 | 2016-09-15 13:04:09 |
|  38 |    1 |    309 |        4 |        1 | 2016-09-12 13:04:01 |
|  42 |    1 |    373 |        4 |        1 | 2016-09-10 13:04:08 |
+-----+------+--------+----------+----------+---------------------+

这省略了 id 41 行，因为它在同一天(第 15 天)内已​​经有一个具有相同 dwable 值的后续条目。同样假设如果 id 41 行在 14 日有一个时间戳，例如它应该被包括在内。

正如其他答案所建议的那样，我尝试在分组之前使用子查询进行排序，如下所示:

SELECT * FROM 
(SELECT * FROM `uc_likes` WHERE `actionto` = 1 ORDER BY time DESC) 
AS t GROUP BY DAY(time), dwable order by time DESC;

然而，这仍然选择 id 41 而不是 id 256。查看下面的结果:

+-----+------+--------+----------+----------+---------------------+
| id  | type | dwable | actionby | actionto | time                |
+-----+------+--------+----------+----------+---------------------+
| 256 |    1 |    372 |        5 |        1 | 2016-09-15 13:04:13 |
| 253 |    1 |    307 |        5 |        1 | 2016-09-15 13:04:12 |
|  55 |    1 |    416 |        4 |        1 | 2016-09-15 13:04:09 |
|  38 |    1 |    309 |        4 |        1 | 2016-09-12 13:04:01 |
|  42 |    1 |    373 |        4 |        1 | 2016-09-10 13:04:08 |
+-----+------+--------+----------+----------+---------------------+

Answer 1

例如:

SELECT x.* 
  FROM uc_likes x 
  JOIN 
     ( SELECT dwable
            , DATE(time) dt
            , MAX(time) time 
         FROM uc_likes 
        GROUP 
           BY dwable
            , dt
     ) y 
    ON y.dwable = x.dwable 
   AND y.dt = DATE(x.time) 
   AND y.time = x.time;
+-----+------+--------+----------+----------+---------------------+
| id  | type | dwable | actionby | actionto | time                |
+-----+------+--------+----------+----------+---------------------+
|  38 |    1 |    309 |        4 |        1 | 2016-09-12 13:04:01 |
|  42 |    1 |    373 |        4 |        1 | 2016-09-10 13:04:08 |
|  55 |    1 |    416 |        4 |        1 | 2016-09-15 13:04:09 |
| 253 |    1 |    307 |        5 |        1 | 2016-09-15 13:04:12 |
| 256 |    1 |    372 |        5 |        1 | 2016-09-15 13:04:13 |
+-----+------+--------+----------+----------+---------------------+