Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use GROUP BY grouping to get the maximum value of the field in MySQL, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's go to know it together.

Suppose you have a business scenario where you need to query user login record information. 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`)

);

And a little more test data:

INSERT INTO tb SELECT null, 1001, '192.168.1.1 dollars,' 2016-01-01 16 purl 30 purl 47'

INSERT INTO tb SELECT null, 1003, '192.168.1.153',' 2016-01-01 1915

INSERT INTO tb SELECT null, 1001, '192.168.1.61', '2016-01-01 16 purl 50 purl 41'

INSERT INTO tb SELECT null, 1002, '192.168.1.31', '2016-01-01 18 purl 30 purl 21'

INSERT INTO tb SELECT null, 1002, '192.168.1.66', '2016-01-01 1915 12 purl 32'

INSERT INTO tb SELECT null, 1001, '192.168.1.81mm,' 2016-01-01 1915

INSERT INTO tb SELECT null, 1001, '192.168.1.231', '2016-01-01 19 55 purl 34'

Table data:

+-- +

| | id | uid | ip | login_time | |

+-- +

| | 1 | 1001 | 192.168.1.1 | 2016-01-01 16:30:47 |

| | 2 | 1003 | 192.168.1.153 | 2016-01-01 19:30:51 |

| | 3 | 1001 | 192.168.1.61 | 2016-01-01 16:50:41 |

| | 4 | 1002 | 192.168.1.31 | 2016-01-01 18:30:21 |

| | 5 | 1002 | 192.168.1.66 | 2016-01-01 19:12:32 |

| | 6 | 1001 | 192.168.1.81 | 2016-01-01 19:53:09 |

| | 7 | 1001 | 192.168.1.231 | 2016-01-01 19:55:34 |

+-- +

If you only need to find out when the user last logged in, you can simply write:

SELECT uid, max (login_time)

FROM tb

GROUP BY uid

+-+ +

| | uid | max (login_time) |

+-+ +

| | 1001 | 2016-01-01 19:55:34 |

| | 1002 | 2016-01-01 19:12:32 |

| | 1003 | 2016-01-01 19:30:51 |

+-+ +

If you need to query other information about the last time the user logged in, you cannot use this sql to write:

-- incorrect writing

SELECT uid, ip, max (login_time)

FROM tb

GROUP BY uid

-- incorrect writing

Such statements are non-SQL standard and can be successfully executed in the MySQL database, but the return is unknown.

(if sql_mode has only_full_group_by turned on, it will not execute successfully. )

It is possible that the ip field will take the value of the first row before uid grouping, which is obviously not the required information

Write ①

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

Write ②

Or write it another way:

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)

By the way, I tested it.

In the previous version, the sql of writing ② was not ideal in the case of a large amount of data, and the visual performance was not good.

In version 5. 6 and later, the sql of ② will be much faster, and the implementation plan has 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 |

+-- +

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 |

+-- +

Write ③

Simply change it directly to join for better performance:

SELECT a.uid, a.ip, a.login_time

FROM (SELECT uid, max (login_time) login_time

FROM tb

GROUP BY uid

) b JOIN tb an ON a.uid = b.uid AND a.login_time = b.login_time

Of course, the results are all the same:

+-+

| | uid | ip | login_time | |

+-+

| | 1003 | 192.168.1.153 | 2016-01-01 19:30:51 |

| | 1002 | 192.168.1.66 | 2016-01-01 19:12:32 |

| | 1001 | 192.168.1.231 | 2016-01-01 19:55:34 |

+-+

Of course. If you want to take the minimum value in groups, just change the corresponding functions and symbols directly.

The above is all the content of the article "how to use GROUP BY to get the maximum value of a field in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.