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`)
);

复制

增加模拟数据

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,
 substring_index( group_concat( DISTINCT task_id ORDER BY task_id ASC SEPARATOR ';'), ';', 2 ) AS taskList 
FROM
 author_info 
GROUP BY
 author_id

复制

函数介绍

group_concat函数

MySQL的group_concat函数是用于将多行数据按照指定的分隔符连接成一个字符串。它的语法如下：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

复制

参数说明

• DISTINCT
  ：可选参数，用于去除重复的值。
• expr
  ：要连接的字段或表达式。
• ORDER BY
  ：可选参数，用于指定连接后的字符串的排序方式。
• ASC
  或DESC
  ：可选参数，用于指定排序的顺序，默认为升序。
• SEPARATOR
  ：可选参数，用于指定连接字符串的分隔符，默认为逗号。

使用场景

• 将多行数据合并成一个字符串，方便进行数据的展示和处理。
• 在查询结果中，将多个字段的值合并成一个字段进行展示。
• 在分组查询中，将分组后的多个值合并成一个字符串进行展示。

参考案例

有一个表格students
，包含id
、name
和course
字段，我们可以使用group_concat函数将同一个学生的多门课程合并成一个字符串：

SELECT id, name, GROUP_CONCAT(course) AS courses
FROM students
GROUP BY id, name;

复制

这样就可以得到每个学生的课程列表。

总结

MySQL
的GROUP_CONCAT
函数适合将多行数据合并为一个字符串的场景，尤其适用于分类、标签、评论等需要聚合数据的场景。

substring_index函数

MySQL
的substring_index
函数用于从一个字符串中获取指定分隔符之前或之后的子字符串。它的语法如下：

SUBSTRING_INDEX(str, delimiter, count)

复制

参数说明

• str
  ：要进行操作的字符串。
• delimiter
  ：分隔符，用于指定字符串的分隔符。
• count
  ：指定要返回的子字符串的个数。如果count
  为正数，则返回从左边开始的子字符串；如果count
  为负数，则返回从右边开始的子字符串。

使用场景

• 提取字符串中的部分内容：可以使用substring_index函数从一个字符串中提取出指定分隔符之前或之后的子字符串。例如，可以使用该函数从URL中提取出域名部分。
• 分割字符串：可以使用substring_index函数将一个字符串按照指定的分隔符进行分割。例如，可以使用该函数将一个逗号分隔的字符串分割成多个子字符串。
• 获取文件名或文件扩展名：可以使用substring_index函数从文件路径中提取出文件名或文件扩展名部分。

参考案例

提取URL中的域名部分：

SELECT SUBSTRING_INDEX('https://www.example.com/test', '/', 3) AS domain;

复制

输出结果：https://www.example.com

分割逗号分隔的字符串：

SELECT SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS fruit1,
       SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', 2), ',', -1) AS fruit2,
       SUBSTRING_INDEX('apple,banana,orange', ',', -1) AS fruit3;

复制

输出结果：apple
、banana
、orange

获取文件名和文件扩展名：

SELECT SUBSTRING_INDEX('path/to/file.txt', '/', -1) AS filename,
       SUBSTRING_INDEX('path/to/file.txt', '.', -1) AS extension;

复制

输出结果：file.txt
、txt

总结

MySQL
的substring_index
函数在处理字符串时非常有用，可以用于提取、分割和获取字符串中的特定部分。它可以在各种场景下使用，例如处理URL、分割字符串和提取文件名等。