MySQL - GROUP BY grouping to get the maximum value of the field sample code details-Mysql Tutorial-php.cn

MySQL - GROUP BY grouping to get the maximum value of the field sample code details

黄舟

Release： 2018-05-15 14:14:58

Original

3167 people have browsed it

MySQL - GROUP BY grouping sample code details to obtain the maximum field value:

Suppose there is a business scenario where user login record information needs to be queried, and the table structure is as follows:

CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `ip` varchar(16) NOT NULL,
  `login_time` datetime,
  PRIMARY KEY (`id`),
  KEY (`uid`)
);

Copy after login

Let’s get some more test data:

INSERT INTO tb SELECT null, 1001, '192.168.1.1', '2017-01-21 16:30:47';
INSERT INTO tb SELECT null, 1003, '192.168.1.153', '2017-01-21 19:30:51';
INSERT INTO tb SELECT null, 1001, '192.168.1.61', '2017-01-21 16:50:41';
INSERT INTO tb SELECT null, 1002, '192.168.1.31', '2017-01-21 18:30:21';
INSERT INTO tb SELECT null, 1002, '192.168.1.66', '2017-01-21 19:12:32';
INSERT INTO tb SELECT null, 1001, '192.168.1.81', '2017-01-21 19:53:09';
INSERT INTO tb SELECT null, 1001, '192.168.1.231', '2017-01-21 19:55:34';

Copy after login

Table data situation:

+----+------+---------------+---------------------+
| id | uid  | ip            | login_time          |
+----+------+---------------+---------------------+
| 1  | 1001 | 192.168.1.1   | 2017-01-21 16:30:47 |
| 2  | 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
| 3  | 1001 | 192.168.1.61  | 2017-01-21 16:50:41 |
| 4  | 1002 | 192.168.1.31  | 2017-01-21 18:30:21 |
| 5  | 1002 | 192.168.1.66  | 2017-01-21 19:12:32 |
| 6  | 1001 | 192.168.1.81  | 2017-01-21 19:53:09 |
| 7  | 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
+----+------+---------------+---------------------+

Copy after login

If you only need to find out the last login time for a user, you can simply write:

SELECT uid, max(login_time)
FROM tb
GROUP BY uid;

Copy after login

+------+---------------------+
| uid  | max(login_time)       |
+------+---------------------+
| 1001 | 2017-01-21 19:55:34 |
| 1002 | 2017-01-21 19:12:32 |
| 1003 | 2017-01-21 19:30:51 |
+------+---------------------+

Copy after login

If you need to query other information about the user's last login, you cannot write it in this kind of SQL:

-- 错误写法
SELECT uid, ip, max(login_time)
FROM tb
GROUP BY uid;
-- 错误写法

Copy after login

Such a statement is not SQL standard, although it can The execution is successful in the MySQL database, but the returned value is unknown
(If sql_mode is turned on only_full_group_by, the execution will not be successful.)

Copy after login

Maybe the ip field will take the first one before the uid group The value of row is obviously not the required information
Writing 1
Write a subquery:

SELECT a.uid, a.ip, a.login_time
FROM tb a
WHERE a.login_time in (
SELECT max(login_time)
FROM tb
GROUP BY uid);

Copy after login

Writing 2
Or change the writing:

SELECT a.uid, a.ip, a.login_time
FROM tb a
WHERE a.login_time = (
SELECT max(login_time)
FROM tb
WHERE a.uid = uid);

Copy after login

Tested it by the way
In versions before 5.6, the execution plan of this SQL statement ② is not ideal when there is a large amount of data, and the visual performance is poor.
In 5.6 and later versions, writing ② this sql will be much faster, and the execution plan has also changed
5.5.50：

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 1  | PRIMARY            | a     | ALL  | NULL             | NULL  | NULL      | NULL | 7    | Using where |
| 2  | DEPENDENT SUBQUERY | tb    | ALL  | uid           | NULL  | NULL      | NULL | 7    | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

Copy after login

5.6.30：

+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref       | rows  | Extra      |
+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
| 1  | PRIMARY            | a     | ALL  | NULL              | NULL | NULL      | NULL        | 7    | Using where |
| 2  | DEPENDENT SUBQUERY | tb    | ref  | uid           | uid  | 4       | test.a.uid | 1    | NULL           |
+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+

Copy after login

Writing method 3
The performance will be better if you directly change it to join:

SELECT a.uid, a.ip, a.login_time
FROM (SELECT uid, max(login_time) login_time
FROM tb
GROUP BY uid
) b JOIN tb a ON a.uid = b.uid AND a.login_time = b.login_time;

Copy after login

Of course, the results are the same:

+------+---------------+---------------------+
| uid  | ip            | login_time          |
+------+---------------+---------------------+
| 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
| 1002 | 192.168.1.66  | 2017-01-21 19:12:32 |
| 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
+------+---------------+---------------------+

Copy after login

Note: If you want to group the minimum value, just change the corresponding function and symbol directly.

The above is the detailed content of MySQL - GROUP BY grouping to get the maximum value of the field sample code details. For more information, please follow other related articles on the PHP Chinese website!