mysql - SQL查询以选择具有最大列值的所有行

Question

CREATE TABLE `user_activity` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `type` enum('request','response') DEFAULT NULL,
  `data` longtext NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `source` varchar(255) DEFAULT NULL,
  `task_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

我有这个数据:-

现在我需要为 user_id=527 选择所有行，其中 created_at 值为最大值。所以我需要这张图片的最后 3 行。

我写了这个查询:-

SELECT * 
FROM   user_activity 
WHERE  user_id = 527 
       AND source = 'E1' 
       AND task_name IN ( 'GetReportTask', 'StopMonitoringUserTask' ) 
       AND created_at = (SELECT Max(created_at) 
                         FROM   user_activity 
                         WHERE  user_id = 527 
                                AND source = 'E1' 
                                AND task_name IN ( 'GetReportTask', 
                                                   'StopMonitoringUserTask' ));

这是非常低效的，因为我再次运行与内部查询完全相同的查询，只是它忽略了 created_at。执行此操作的正确方法是什么？

Answer 1

我会使用相关子查询:

SELECT ua.* 
FROM user_activity ua
WHERE ua.user_id = 527 AND source = 'E1' AND
      ua.task_name IN ('GetReportTask', 'StopMonitoringUserTask' ) AND
      ua.created_at = (SELECT MAX(ua2.created_at) 
                       FROM user_activity ua2
                       WHERE ua2.user_id = ua.user_id AND 
                             ua2.source = ua.source AND
                             ua2.task_name IN ( 'GetReportTask', 'StopMonitoringUserTask' )
                      );

虽然这看起来效率不高，但您可以在 user_activity(user_id, source, task_name, created_at) 上创建索引。有了这个索引，查询应该有不错的性能。