MySQL分组查询，获取分组后数据

IT后浪

已于 2024-07-13 22:05:32 修改

阅读量3k

点赞数

分类专栏： mysql 文章标签： mysql 数据库

于 2022-06-21 15:58:05 首次发布

本文链接：https://blog.csdn.net/aaalk1001/article/details/125392893

版权

mysql 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

MySQL分组查询，获取分组后数据

在这里插入图片描述

MySQL分组查询，将其它列的数据，合并到一行展示，可以设置去重，设置去重，设置排序，截取指定条数

创建表结构

CREATE TABLE `author_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author_id` int(11) DEFAULT NULL,
  `author_name` varchar(255) DEFAULT NULL,
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

增加模拟数据

INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (1, 1, '张三', 101);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (2, 1, '张三', 102);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (3, 1, '张三', 103);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (4, 2, '李四', 104);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (5, 2, '李四', 105);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (6, 2, '李四', 106);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (7, 3, '王五', 101);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (8, 3, '王五', 102);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (9, 3, '王五', 103);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (10, 4, '赵六', 104);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (11, 4, '赵六', 105);
INSERT INTO `author_info` (`id`, `author_id`, `author_name`, `task_id`) VALUES (12, 4, '赵六', 106);

查询数据

SELECT
	author_id,
	author_name,
	substring_index( group_concat( DISTINCT task_id ORDER BY task_id ASC ), ',', 3 ) AS taskList 
FROM
	author_info 
GROUP BY
	author_id