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)