Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql's group by grammar handout, the contents of the article are carefully selected and edited by the author, mysql's group by grammar handout has a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the next topic content.

The group by syntax of mysql can group data according to the specified rules. Grouping is to divide a data set into several small regions, and then process the data for several small areas. This article will introduce the method of sorting within a group when mysql uses group by to group groups.

Related mysql video tutorials

The group by syntax of mysql can group the data, but the grouped data cannot be sorted within the group.

For example, a comment table has multiple user comments, and you need to get the content of each user's last comment.

Create test data tables and data

CREATE TABLE `comment` (`id`comment` (10) unsigned NOT NULL AUTO_INCREMENT, `user_ id` int (10) unsigned NOT NULL, `content` varchar (200) NOT NULL, `addtime` datetime NOT NULL, `lastmodify` datetime NOT NULL, PRIMARY KEY (`id`), KEY `user_ id` (`user_ id`), KEY `addtime` (`addtime`), KEY `uid_ addtime` (`user_ id`, `addtime`) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `comment` (`id`, `commentid`, `content`, `addtime`, `lastmodify`) VALUES (1,1, 'comment 1,' 2017-05-17 00 VALUES, '2017-05-17 00 VALUES'), (2, 1, 'comment 2,' 2017-05-17 00 VALUES 01, '2017-05-17 00 VALUES 01'), (3, 2, 'comment 1,' comment 1, '2017-05-17 00 VALUES 02') ), (4, 2, 'comment 2,' 2017-05-17 00, 2017-05-17 00), (5, 3, 'comment 1,' 2017-05-17 00, 00, 00, 04'), (6, 1, 'comment 3,' 2017-05-17 2017-05-17 00, 00, 00, 04') ), (7, 4, 'comment 1,' 2017-05-17 00, 2017-05-17 00), (8, 4, 'comment 2,' 2017-05-17 00, 00, 00, 00, 07'), (9, 4, 'comment 3,' 2017-05-17, 2017-05-17, 000000, 07'), (9, 4, 'comment 3,' 2017-05-17 0000, 08' ), (10, 4, 'comment 4,' 2017-05-17 00, 00, 00, 09), (11, 3, 'comment 2,' 2017-05-17 00, 00, 00, 10, 10, 2017-05-17, 00, 00, 0010)) Select * from comment +-+ | id | user_id | content | addtime | lastmodify | + -+-+ | 1 | 1 | comment 1 | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 | 2 | 1 | comment 2 | 2017-05-17 00:00:01 | 2017-05-17 00:00:01 | | 3 | 2 | comment 1 | | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 | | 4 | 2 | comment 2 | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 | | 5 | 3 | comment 1 | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 | 6 | 1 | comment 3 | 2017-05-17 00:00:05 | 2017-05-17 00 | : 00:05 | | 7 | 4 | comment 1 | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 | 8 | 4 | comment 2 | 2017-05-17 00:00:07 | 2017-05-17 00:00:07 | 9 | 4 | comment 3 | 2017-05-17 00:00:08 | 2017-05-17 00:00:08 | 10 | 4 | comment 4 | 2017 -05-17 00:00:09 | 2017-05-17 00:00:09 | | 11 | 3 | comment 2 | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 | +-

In the comment table, the last comment made by each user is the record of id for 6, 4, 11, 10.

Use group by query

Select * from comment group by user_id +-+ | id | user_id | content | addtime | lastmodify | + -+-+ | 1 | 1 | comment 1 | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 | 3 | 2 | comment 1 | 2017-05-17 00:00:02 | 2017-05-17 00:00:02 | 5 | 3 | comment 1 | | 2017-05-17 00:00:04 | 2017-05-17 00:00:04 | 7 | 4 | comment 1 | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 | +-

You can see the result. After grouping, only the first piece of data in the group is returned. Because the group by syntax does not have the ability to sort within a group, it is only displayed by the default sort of mysql.

How to sort the data in the group by packet needs to be handled according to different requirements.

1.id is the biggest, and the comment time must be the latest.

In this case, we can use id instead of time to search and sort within the group, and use max (id) to get the largest comment id (that is, the latest comments) in each group.

Select * from comment where id in (select max (id) from comment group by user_id) order by user_id +-+ | id | user_id | content | addtime | lastmodify | + -+-+ | 6 | 1 | comment 3 | 2017-05-17 00:00:05 | 2017-05-17 00:00:05 | 4 | 2 | comment 2 | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 | | 11 | 3 | comment 2 | | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 | 10 | 4 | comment 4 | 2017-05-17 00:00:09 | 2017-05-17 00:00:09 | +-

2.id has nothing to do with comment time. Id's big comment time may not be up to date.

In this case, we need to use max (addtime) to get the latest comments, but because different users may have the same comment time, we also need to add more user_id to query.

Recreate test data

Truncate table comment INSERT INTO `comment` (`id`, `commentid`, `content`, `addtime`, `lastmodify`) VALUES (1,1, 'comment 1,' 2017-05-17 00 VALUES 00, '2017-05-17 00 VALUES'), (2, 1, 'comment 2,' 2017-05-17 00 VALUES 10 VALUES 01'), (3, 2, 'comment 1,' comment 1, '2017-05-17 00 VALUES 1015') ), (4, 2, 'comment 2,' 2017-05-17 00, 2017-05-17 00, 2017-05-17 00, 100), (5, 3, 'comment 1,' 2017-05-17 00, 10, 10, 04), (6, 1, 'comment 3,' 2017-05-17, 2017-05-17, 00, 00, 10, 04') (7, 4, 'comment 1,' 2017-05-17 00, 2017-05-17 00), (8, 4, 'comment 2,' 2017-05-17 00 10, 7), (9, 4, 'comment 3,' 2017-05-17 2017-05-17 00 00 08') ), (10, 4, 'comment 4,' 2017-05-17 00, 00, 00, 09), (11, 3, 'comment 2,' 2017-05-17 00, 00, 00, 10, 10, 2017-05-17, 00, 00, 0010)) Select * from comment +-+ | id | user_id | content | addtime | lastmodify | + -+-+ | 1 | 1 | comment 1 | 2017-05-17 00:00:00 | 2017-05-17 00:00:00 | 2 | 1 | comment 2 | 2017-05-17 00:10:01 | 2017-05-17 00:10:01 | | 3 | 2 | comment 1 | | 2017-05-17 00:10:02 | 2017-05-17 00:10:02 | | 4 | 2 | comment 2 | 2017-05-17 00:00:03 | 2017-05-17 00:00:03 | | 5 | 3 | comment 1 | 2017-05-17 00:10:04 | 2017-05-17 00:10:04 | 6 | 1 | comment 3 | 2017-05-17 00:00:05 | 2017-05-17 00 | : 00:05 | | 7 | 4 | comment 1 | 2017-05-17 00:00:06 | 2017-05-17 00:00:06 | 8 | 4 | comment 2 | 2017-05-17 00:10:07 | 2017-05-17 00:10:07 | 9 | 4 | comment 3 | 2017-05-17 00:00:08 | 2017-05-17 00:00:08 | 10 | 4 | comment 4 | 2017 -05-17 00:00:09 | 2017-05-17 00:00:09 | | 11 | 3 | comment 2 | 2017-05-17 00:00:10 | 2017-05-17 00:00:10 | +-

Those who meet the requirements should be the record of id for 2pm 3pm 5pm 8.

Select a.* from comment as a right join (select user_id, max (addtime) as maxtime from comment where user_id is not null group by user_id) as b on a.user_id=b.user_id and a.addtime=b.maxtime order by a.user_id asc +-+ | id | user_id | content | addtime | lastmodify | +- -+ | 2 | 1 | comment 2 | 2017-05-17 00:10:01 | 2017-05-17 00:10:01 | | 3 | 2 | comment 1 | 2017-05-17 00:10:02 | 2017-05-17 00:10:02 | 5 | 3 | comment 1 | 2017-05-17 00:10:04 | 2017-05-17 00:10:04 | | 8 | 4 | comment 2 | 2017-05-17 00:10:07 | 2017-05-17 00:10:07 | +- -+

Using right join, you can reduce the outer dataset.

Where user_id is not null enables indexes to be used when group by user_id.

After reading the above group by grammar handouts on mysql, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

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.