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

3406 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, &#39;192.168.1.1&#39;, &#39;2017-01-21 16:30:47&#39;;
INSERT INTO tb SELECT null, 1003, &#39;192.168.1.153&#39;, &#39;2017-01-21 19:30:51&#39;;
INSERT INTO tb SELECT null, 1001, &#39;192.168.1.61&#39;, &#39;2017-01-21 16:50:41&#39;;
INSERT INTO tb SELECT null, 1002, &#39;192.168.1.31&#39;, &#39;2017-01-21 18:30:21&#39;;
INSERT INTO tb SELECT null, 1002, &#39;192.168.1.66&#39;, &#39;2017-01-21 19:12:32&#39;;
INSERT INTO tb SELECT null, 1001, &#39;192.168.1.81&#39;, &#39;2017-01-21 19:53:09&#39;;
INSERT INTO tb SELECT null, 1001, &#39;192.168.1.231&#39;, &#39;2017-01-21 19:55:34&#39;;

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.)

<br/>

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!