前言

我们在做项目写sql语句的时候，是否会遇到这样的场景，就是需要把查询出来的多列，按照字符串分割合并成一列显示，或者把存在数据库里面用逗号分隔的一列，查询分成多列呢，常见场景有，文章标签，需要吧查询多个标签合并成一列，等，需要怎么去实现呢，这就涉及到MySQL的字符串操作

group_concat

场景再现我想把查询多列数据合并成一列显示用逗号分隔就需要用到group_concat这个函数

下面sql语句

1select r.ROLE_NAME
2from t_user u
3         right join t_user_role ur on ur.USER_ID = u.USER_ID,
4     t_role r
5where r.ROLE_ID = ur.ROLE_ID
6  and u.USER_ID = 7

ID为7的用户有两个角色，但是我现在想把它显示成一列，就需要用到字符串函数group_concat 如下sql

1select group_concat(r.ROLE_NAME)
2from t_user u
3         right join t_user_role ur on ur.USER_ID = u.USER_ID,
4     t_role r
5where r.ROLE_ID = ur.ROLE_ID
6  and u.USER_ID = 7;

实现了我需要的功能

当然group_concat函数默认使用逗号，进行连接，我们也可以自己指定分隔连击符如group_concat(name separator ';')

1select group_concat(r.ROLE_NAME separator ';')
2from t_user u
3         right join t_user_role ur on ur.USER_ID = u.USER_ID,
4     t_role r
5where r.ROLE_ID = ur.ROLE_ID
6  and u.USER_ID = 7;

当然实际应用不单单这么简单，需要结合子查询使用，

如下sql 查询用户详细信息，就包括用户角色信息，部门信息等

1select tu.*,
2       d.DEPT_NAME,
3       (select group_concat(r.ROLE_NAME)
4        from t_user u
5                 left join t_user_role ur on ur.USER_ID = u.USER_ID,
6             t_role r
7        where r.ROLE_ID = ur.ROLE_ID
8          and u.USER_ID = tu.USER_ID) as roles
9from t_user tu
10         left join
11     t_dept d
12     on tu.DEPT_ID = d.DEPT_ID
13where tu.USER_ID=7;

substring_index(str,delim,count)

场景在现某些业务表出于历史原因或性能原因，都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值。如下表中的 theme 所示：

这种情况下，可以考虑将该列根据分隔符进行分割，形成多个列就需要使用到substring_index函数

1SUBSTRING_INDEX(str,delim,count)   
2-- str: 被分割的字符串; delim: 分隔符; count: 分割符出现的次数

对于字符串 “1,2,3” ，设置delim为 “,”，count为1，就会返回 “1”；其它参数不变，count为2，就会返回 “1,2”；其它参数不变，count为-1，就会返回 “3”。

如下sql

1select USERNAME,
2       (select substring_index(tu.THEME, ',', 1) from t_user tu where tu.USER_ID = 1) theme1,
3       (select substring_index(tu.THEME, ',', 2) from t_user tu where tu.USER_ID = 1) theme2,
4       (select substring_index(tu.THEME, ',', -1) from t_user tu where tu.USER_ID = 1) theme3
5from t_user
6where USER_ID = 1;

显然不符合我们所需要的，我们可以在嵌套sql自查询实现如下

1select USERNAME,
2       (select substring_index(tu.THEME, ',', 1) from t_user tu where tu.USER_ID = 1) theme1,
3       (select substring_index((select substring_index(tu.THEME, ',', 2) from t_user tu where tu.USER_ID = 1),',',-1) theme2),
4       (select substring_index(tu.THEME, ',', -1) from t_user tu where tu.USER_ID = 1) theme3
5from t_user
6where USER_ID = 1;

当然这个计算应该是动态的可以参考参考实现

MySQL查询结果集字符串操作之多行合并与单行分割

前言

group_concat

substring_index(str,delim,count)

评论区

相关推荐