mysql查询获取分组后最新的一条记录,策略：在子查询中先排序,然后再分组查询(默认第一条),就是最新的一条数据了
为测试这个种策略，我们首先进行如下步骤：
1、创建表格，并且插入数据 /* Navicat Premium Data Transfer
Source Server : localhost Source Server Type : MySQL Source Server Version : 50173 Source Host : localhost Source Database : yyz_test
Target Server Type : MySQL Target Server Version : 50173 File Encoding : utf-8
Date: 08/12/2017 12:26:17 PM */
SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0;
-- ---------------------------- -- Table structure for `objective_questions_records` -- ---------------------------- DROP TABLE IF EXISTS `objective_questions_records`; CREATE TABLE `objective_questions_records` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `uuid` varchar(255) NOT NULL, `utime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `is_right` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ---------------------------- -- Records of `objective_questions_records` -- ---------------------------- BEGIN; INSERT INTO `objective_questions_records` VALUES ('1214', '548126', '828a333', '2017-08-12 10:36:28', '0'), ('1215', '334243', '8aac4907508d5d3d0150bbefc20e4ba5', '2017-08-06 10:36:14', '1'), ('1232', '3748549', '29320a8813514a5bb5a28a605ddbc5a2', '2017-07-04 10:39:24', '2'), ('2133', '1234545', '29320a8813514a5bb5a28a605ddbc5a2', '2017-08-08 10:38:09', '3'), ('2134', '334243', '29320a8813514a5bb5a28a605ddbc5a2', '2017-06-14 10:38:12', '5'), ('2145', '548126', '828a333', '2017-08-12 11:20:36', '6'), ('2344', '334243', '3342432weowwej', '2017-08-12 11:19:27', '4'), ('2919', '334243', '29320a8813514a5bb5a28a605ddbc5a2', '2016-03-15 18:12:25', '1'), ('2921', '334243', '8aac4907508d5d3d0150bbefc20e4ba5', '2017-08-02 10:36:27', '0'); COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
2.最终找到的MySql语句，获得了自己所需要的效果数据
mysql> select * from objective_questions_records; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 1214 | 548126 | 828a333 | 2017-08-12 10:36:28 | 0 | | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | | 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 | | 2134 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-06-14 10:38:12 | 5 | | 2145 | 548126 | 828a333 | 2017-08-12 11:20:36 | 6 | | 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 | | 2919 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2016-03-15 18:12:25 | 1 | | 2921 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-02 10:36:27 | 0 | +------+---------+----------------------------------+---------------------+----------+ 9 rows in set (0.00 sec)
mysql> select * from (select * from objective_questions_records order by utime desc ) as R group by user_id,uuid order by utime desc; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 2145 | 548126 | 828a333 | 2017-08-12 11:20:36 | 6 | | 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 | | 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 | | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | | 2134 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-06-14 10:38:12 | 5 | +------+---------+----------------------------------+---------------------+----------+ 6 rows in set (0.00 sec)
3.下面是测试MySql命令的过程； mysql> show create table objective_questions_records \G; *************************** 1. row *************************** Table: objective_questions_records Create Table: CREATE TABLE `objective_questions_records` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `uuid` varchar(255) NOT NULL, `utime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `is_right` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified
mysql> select * from objective_questions_records; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 1214 | 548126 | 828a333 | 2017-08-12 10:36:28 | 0 | | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | | 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 | | 2134 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-06-14 10:38:12 | 5 | | 2145 | 548126 | 828a333 | 2017-08-12 11:20:36 | 6 | | 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 | | 2919 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2016-03-15 18:12:25 | 1 | | 2921 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-02 10:36:27 | 0 | +------+---------+----------------------------------+---------------------+----------+ 9 rows in set (0.00 sec)
mysql> select * from objective_questions_records order by utime desc; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 2145 | 548126 | 828a333 | 2017-08-12 11:20:36 | 6 | | 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 | | 1214 | 548126 | 828a333 | 2017-08-12 10:36:28 | 0 | | 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 | | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | | 2921 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-02 10:36:27 | 0 | | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | | 2134 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-06-14 10:38:12 | 5 | | 2919 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2016-03-15 18:12:25 | 1 | +------+---------+----------------------------------+---------------------+----------+ 9 rows in set (0.00 sec)
mysql> select * from objective_questions_records order by utime; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 2919 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2016-03-15 18:12:25 | 1 | | 2134 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-06-14 10:38:12 | 5 | | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | | 2921 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-02 10:36:27 | 0 | | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | | 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 | | 1214 | 548126 | 828a333 | 2017-08-12 10:36:28 | 0 | | 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 | | 2145 | 548126 | 828a333 | 2017-08-12 11:20:36 | 6 | +------+---------+----------------------------------+---------------------+----------+ 9 rows in set (0.00 sec)
mysql> select * from objective_questions_records group by uuid; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | | 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 | | 1214 | 548126 | 828a333 | 2017-08-12 10:36:28 | 0 | | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | +------+---------+----------------------------------+---------------------+----------+ 4 rows in set (0.00 sec)
mysql> select * from objective_questions_records group by user_id; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | | 1214 | 548126 | 828a333 | 2017-08-12 10:36:28 | 0 | | 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 | | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | +------+---------+----------------------------------+---------------------+----------+ 4 rows in set (0.00 sec)
mysql> select * from objective_questions_records group by user_id,uuid; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 2134 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-06-14 10:38:12 | 5 | | 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 | | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | | 1214 | 548126 | 828a333 | 2017-08-12 10:36:28 | 0 | | 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 | | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | +------+---------+----------------------------------+---------------------+----------+ 6 rows in set (0.00 sec)
mysql> select * from (select * from objective_questions_records order by utime desc ) as R group by user_id,uuid order by utime desc; +------+---------+----------------------------------+---------------------+----------+ | id | user_id | uuid | utime | is_right | +------+---------+----------------------------------+---------------------+----------+ | 2145 | 548126 | 828a333 | 2017-08-12 11:20:36 | 6 | | 2344 | 334243 | 3342432weowwej | 2017-08-12 11:19:27 | 4 | | 2133 | 1234545 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-08-08 10:38:09 | 3 | | 1215 | 334243 | 8aac4907508d5d3d0150bbefc20e4ba5 | 2017-08-06 10:36:14 | 1 | | 1232 | 3748549 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-07-04 10:39:24 | 2 | | 2134 | 334243 | 29320a8813514a5bb5a28a605ddbc5a2 | 2017-06-14 10:38:12 | 5 | +------+---------+----------------------------------+---------------------+----------+ 6 rows in set (0.00 sec)