當前位置:網站首頁>MySQL查詢結果集字符串操作之多行合並與單行分割

MySQL查詢結果集字符串操作之多行合並與單行分割

2022-01-27 12:44:26 程序員社區

MySQL查詢結果集字符串操作之多行合並與單行分割 , 你了解嗎? 本文就為大家帶來了一篇 MySQL查詢結果集字符串操作之多行合並與單行分割 一起看看吧!
另外小編還收集了很多不錯的編程資源,希望對你有幫助:點擊查看
祝您生活愉快~

前言

我們在做項目寫sql語句的時候,是否會遇到這樣的場景,就是需要把查詢出來的多列,按照字符串分割合並成一列顯示,或者把存在數據庫裏面用逗號分隔的一列,查詢分成多列呢,常見場景有,文章標簽,需要吧查詢多個標簽合並成一列,等,需要怎麼去實現呢,這就涉及到MySQL的字符串操作

group_concat

場景再現 我想把查詢多列數據合並成一列顯示用逗號分隔就需要用到group_concat這個函數

下面sql語句

select r.ROLE_NAMEfrom t_user u         right join t_user_role ur on ur.USER_ID = u.USER_ID,     t_role rwhere r.ROLE_ID = ur.ROLE_ID  and u.USER_ID = 7
MySQL查詢結果集字符串操作之多行合並與單行分割插圖
image

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 rwhere r.ROLE_ID = ur.ROLE_ID  and u.USER_ID = 7;
MySQL查詢結果集字符串操作之多行合並與單行分割插圖1
image

實現了我需要的功能

當然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 rwhere r.ROLE_ID = ur.ROLE_ID  and u.USER_ID = 7;
MySQL查詢結果集字符串操作之多行合並與單行分割插圖2
image

當然實際應用不單單這麼簡單,需要結合子查詢使用,

如下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 rolesfrom t_user tu         left join     t_dept d     on tu.DEPT_ID = d.DEPT_IDwhere tu.USER_ID=7;
MySQL查詢結果集字符串操作之多行合並與單行分割插圖3
image

substring_index(str,delim,count)

場景在現某些業務錶出於曆史原因或性能原因,都使用了違反第一範式的設計模式。即同一個列中存儲了多個屬性值。如下錶中的 theme 所示:

MySQL查詢結果集字符串操作之多行合並與單行分割插圖4
image

這種情况下,可以考慮將該列根據分隔符進行分割,形成多個列就需要使用到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) theme3from t_userwhere USER_ID = 1;
MySQL查詢結果集字符串操作之多行合並與單行分割插圖5
image

顯然不符合我們所需要的,我們可以在嵌套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) theme3from t_userwhere USER_ID = 1;
MySQL查詢結果集字符串操作之多行合並與單行分割插圖6
image

當然這個計算應該是動態的可以參考參考實現

版權聲明
本文為[程序員社區]所創,轉載請帶上原文鏈接,感謝
https://cht.chowdera.com/2022/01/202201271244259385.html

隨機推薦