mysql多行数据合并一行(SQL的函数使用方法)

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

group_concat#

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

下面sql语句

select r.ROLE_NAME
from t_user u
         right join t_user_role ur on ur.USER_ID = u.USER_ID,
     t_role r
where r.ROLE_ID = ur.ROLE_ID
  and u.USER_ID = 7
mysql多行数据合并一行(SQL的函数使用方法)

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

select group_concat(r.ROLE_NAME)
from t_user u
         right join t_user_role ur on ur.USER_ID = u.USER_ID,
     t_role r
where r.ROLE_ID = ur.ROLE_ID
  and u.USER_ID = 7;
mysql多行数据合并一行(SQL的函数使用方法)

实现了我需要的功能

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

select group_concat(r.ROLE_NAME separator ';')
from t_user u
         right join t_user_role ur on ur.USER_ID = u.USER_ID,
     t_role r
where r.ROLE_ID = ur.ROLE_ID
  and u.USER_ID = 7;
mysql多行数据合并一行(SQL的函数使用方法)

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

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

select tu.*,
       d.DEPT_NAME,
       (select group_concat(r.ROLE_NAME)
        from t_user u
                 left join t_user_role ur on ur.USER_ID = u.USER_ID,
             t_role r
        where r.ROLE_ID = ur.ROLE_ID
          and u.USER_ID = tu.USER_ID) as roles
from t_user tu
         left join
     t_dept d
     on tu.DEPT_ID = d.DEPT_ID
where tu.USER_ID=7;
mysql多行数据合并一行(SQL的函数使用方法)

substring_index(str,delim,count)#

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

mysql多行数据合并一行(SQL的函数使用方法)

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

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

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

如下sql

select USERNAME,
       (select substring_index(tu.THEME, ',', 1) from t_user tu where tu.USER_ID = 1) theme1,
       (select substring_index(tu.THEME, ',', 2) from t_user tu where tu.USER_ID = 1) theme2,
       (select substring_index(tu.THEME, ',', -1) from t_user tu where tu.USER_ID = 1) theme3
from t_user
where USER_ID = 1;
mysql多行数据合并一行(SQL的函数使用方法)

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

select USERNAME,
       (select substring_index(tu.THEME, ',', 1) from t_user tu where tu.USER_ID = 1) theme1,
       (select substring_index((select substring_index(tu.THEME, ',', 2) from t_user tu where tu.USER_ID = 1),',',-1) theme2),
       (select substring_index(tu.THEME, ',', -1) from t_user tu where tu.USER_ID = 1) theme3
from t_user
where USER_ID = 1;
mysql多行数据合并一行(SQL的函数使用方法)

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

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至2705686032@qq.com 举报,一经查实,本站将立刻删除。原文转载: 原文出处:

(0)
尊云-小张的头像尊云-小张
上一篇 2024 年 5 月 19 日 09:09
下一篇 2024 年 5 月 19 日 09:17

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

联系我们

400-900-3935

在线咨询: QQ交谈

邮件:cong@zun.com

工作时间:365天无休服务 24小时在线

添加微信