测试数据
CREATE TABLE `book_comment` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `like_num` int(10) NOT NULL DEFAULT '0' COMMENT '点赞数', `content` varchar(200) NOT NULL, `addtime` datetime NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `addtime` (`addtime`), KEY `uid_addtime` (`user_id`,`addtime`)) ENGINE=InnoDB;INSERT INTO `book_comment` VALUES ('1', '1', '0', '评论1', '2017-05-17 00:00:00');INSERT INTO `book_comment` VALUES ('2', '1', '2', '评论2', '2017-05-17 00:03:01');INSERT INTO `book_comment` VALUES ('3', '2', '3', '评论3', '2017-05-17 00:03:02');INSERT INTO `book_comment` VALUES ('4', '2', '4', '评论4', '2017-05-17 00:00:03');INSERT INTO `book_comment` VALUES ('5', '3', '0', '评论5', '2017-05-17 00:00:04');INSERT INTO `book_comment` VALUES ('6', '1', '1', '评论6', '2017-05-17 00:00:05');INSERT INTO `book_comment` VALUES ('7', '4', '0', '评论7', '2017-05-17 00:00:06');INSERT INTO `book_comment` VALUES ('8', '4', '2', '评论8', '2017-05-17 00:00:07');INSERT INTO `book_comment` VALUES ('9', '4', '0', '评论9', '2017-05-17 00:00:08');INSERT INTO `book_comment` VALUES ('10', '4', '3', '评论10', '2017-05-17 00:00:09');INSERT INTO `book_comment` VALUES ('11', '3', '2', '评论11', '2017-05-17 00:00:10');1：惯例group语句
mysql> SELECT *,count(*) as num from book_comment GROUP BY user_id;+----+---------+----------+---------+---------------------+-----+| id | user_id | like_num | content | addtime | num |+----+---------+----------+---------+---------------------+-----+| 1 | 1 | 0 | 评论1 | 2017-05-17 00:00:00 | 3 || 3 | 2 | 3 | 评论3 | 2017-05-17 00:03:02 | 2 || 5 | 3 | 0 | 评论5 | 2017-05-17 00:00:04 | 2 || 7 | 4 | 0 | 评论7 | 2017-05-17 00:00:06 | 4 |+----+---------+----------+---------+---------------------+-----+依据用户分组，分组内默认显示第一条数据
2：显示分组中最新的数据
mysql> SELECT *,count(*) as num from book_comment GROUP BY user_id desc;+----+---------+----------+---------+---------------------+-----+| id | user_id | like_num | content | addtime | num |+----+---------+----------+---------+---------------------+-----+| 10 | 4 | 3 | 评论10 | 2017-05-17 00:00:09 | 4 || 11 | 3 | 2 | 评论11 | 2017-05-17 00:00:10 | 2 || 4 | 2 | 4 | 评论4 | 2017-05-17 00:00:03 | 2 || 6 | 1 | 1 | 评论6 | 2017-05-17 00:00:05 | 3 |+----+---------+----------+---------+---------------------+-----+3：减少显示用户最大点赞数
mysql> SELECT *,count(*),max(like_num) as num from book_comment GROUP BY user_id desc;+----+---------+----------+---------+---------------------+----------+-----+| id | user_id | like_num | content | addtime | count(*) | num |+----+---------+----------+---------+---------------------+----------+-----+| 10 | 4 | 3 | 评论10 | 2017-05-17 00:00:09 | 4 | 3 || 11 | 3 | 2 | 评论11 | 2017-05-17 00:00:10 | 2 | 2 || 4 | 2 | 4 | 评论4 | 2017-05-17 00:00:03 | 2 | 4 || 6 | 1 | 1 | 评论6 | 2017-05-17 00:00:05 | 3 | 2 |+----+---------+----------+---------+---------------------+----------+-----+4:获取不同用户点赞数最多的那一行
mysql> SELECT a.*FROM book_comment AS aINNER JOIN ( SELECT user_id, max(like_num) AS like_num FROM book_comment GROUP BY user_id) AS b ON a.user_id = b.user_idAND a.like_num = b.like_numORDER BY a.user_id ASC;+----+---------+----------+---------+---------------------+| id | user_id | like_num | content | addtime |+----+---------+----------+---------+---------------------+| 2 | 1 | 2 | 评论2 | 2017-05-17 00:03:01 || 4 | 2 | 4 | 评论4 | 2017-05-17 00:00:03 || 11 | 3 | 2 | 评论11 | 2017-05-17 00:00:10 || 10 | 4 | 3 | 评论10 | 2017-05-17 00:00:09 |+----+---------+----------+---------+---------------------+5: 其余分组排序办法
利用窗口函数性能(mysql8版本新增)实现分组排序功能