主頁 > 知識(shí)庫 > MySQL中將一列以逗號(hào)分隔的值行轉(zhuǎn)列的實(shí)現(xiàn)

MySQL中將一列以逗號(hào)分隔的值行轉(zhuǎn)列的實(shí)現(xiàn)

熱門標(biāo)簽:信陽穩(wěn)定外呼系統(tǒng)運(yùn)營商 南通自動(dòng)外呼系統(tǒng)軟件 湖南人工外呼系統(tǒng)多少錢 石家莊電商外呼系統(tǒng) 百度地圖圖標(biāo)標(biāo)注中心 申請(qǐng)外呼電話線路 日照旅游地圖標(biāo)注 芒果電話機(jī)器人自動(dòng)化 廣東人工電話機(jī)器人

前言

有時(shí)會(huì)遇到?jīng)]有遵守第一范式設(shè)計(jì)模式的業(yè)務(wù)表。即一列中存儲(chǔ)了多個(gè)屬性值。如下表

pk value
1 ET,AT
2 AT,BT
3 AT,DT
4 DT,CT,AT

一般有這兩種常見需求(測(cè)試數(shù)據(jù)見文末)

1.得到所有的不重復(fù)的值,如

value
AT
BT
CT
DT
ET

SQL如下:

select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1))
from
 (select group_concat(distinct `value`) as col from `row_to_col`) as a
join
 mysql.help_topic as b
on b.help_topic_id  (char_length(a.col) - char_length(replace(a.col,',',''))+1)

2.顯示每個(gè)值及其對(duì)應(yīng)主鍵,如

pk value
1 ET
1 AT
2 AT
2 BT
3 AT
3 DT
4 DT
4 CT
4 AT

SQL如下:

select a.pk,substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)
from
 (select `value` as col,pk from `row_to_col`) as a
join
 mysql.help_topic as b
on b.help_topic_id  (char_length(a.col) - char_length(replace(a.col,',',''))+1)

實(shí)現(xiàn)思路:

需求1:

1. 通過group_concat函數(shù)將value列的值拼接成一個(gè)逗號(hào)隔開的字符串,然后通過substring_index函數(shù)對(duì)字符串進(jìn)行截取
2. 通過substring_index函數(shù)特性,我們就需要知道字符串有多少個(gè)逗號(hào),并且要告訴每個(gè)逗號(hào)的位置
3. 逗號(hào)個(gè)數(shù)=char_length(字符串)-char_length(replace(字符串,',',''))
4. 逗號(hào)位置=mysql.help_topic.id 逗號(hào)個(gè)數(shù)[+1]
5. 最后通過distinct函數(shù)將截取后的單個(gè)值進(jìn)行去重

注意:
1. mysql.help_topic表的自增id是從0開始,所以在進(jìn)行截取時(shí)要對(duì)id進(jìn)行+1。見: substring_index(a.col,',',b.help_topic_id+1)
2. value列最后一個(gè)字符不是逗號(hào)時(shí):逗號(hào)個(gè)數(shù)+1是為了截取時(shí)不漏掉最后一個(gè)逗號(hào)后的值,即: char_length(a.col) - char_length(replace(a.col,',',''))+1;
value列最后一個(gè)字符是逗號(hào)時(shí):逗號(hào)個(gè)數(shù)就不需要+1了,直接:char_length(a.col) - char_length(replace(a.col,',',''))
3. 因?yàn)榻厝r(shí)id要+1,所以在連接時(shí)取的 ,而不是 = 。見:b.help_topic_id (char_length(a.col) - char_length(replace(a.col,',',''))[+1])
4. mysql.help_topic(mysql version: 5.7.21-1)表的自增id,最大值為636。如果group_concat后的字符串中逗號(hào)個(gè)數(shù)大于該值,需要自己單獨(dú)處理自增id的值

需求2:思路基本與需求1同,只是最后的查詢不一樣

涉及到的函數(shù):

length:返回字符串所占的字節(jié)數(shù),是計(jì)算字段的長度。一個(gè)漢字或是中文符號(hào)是算三個(gè)字符,一個(gè)數(shù)字或字母或英文符號(hào)算一個(gè)字符。
char_length:返回字符串所占的字符數(shù),不管漢字還是數(shù)字或者是字母或者符號(hào)(不分中英文)都算是一個(gè)字符。
replace(str,old_string,new_string): 將字符串中str中所有的old_string替換成new_string。
substring_index(被截取字段,關(guān)鍵字,關(guān)鍵字出現(xiàn)的次數(shù)):截取字符串。如果關(guān)鍵字出現(xiàn)的次數(shù)是負(fù)數(shù) , 則是從后倒數(shù),到字符串結(jié)束。
group_concat([DISTINCT] 要連接的字段(可多個(gè),逗號(hào)隔開) [Order BY 排序字段 ASC/DESC] [Separator '分隔符']):
 將group by產(chǎn)生的同一個(gè)分組中的值連接起來,返回一個(gè)字符串結(jié)果。分隔符,默認(rèn)逗號(hào)。

 測(cè)試數(shù)據(jù):

DROP TABLE IF EXISTS `row_to_col`;
CREATE TABLE `row_to_col` (
 `pk` int(11) NOT NULL AUTO_INCREMENT,
 `value` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `row_to_col` VALUES ('1', 'ET,AT');
INSERT INTO `row_to_col` VALUES ('2', 'AT,BT');
INSERT INTO `row_to_col` VALUES ('3', 'AT,DT');
INSERT INTO `row_to_col` VALUES ('4', 'DT,CT,AT');

參考:
https://blog.csdn.net/liuzhoulong/article/details/51729168
https://blog.csdn.net/ldl22847/article/details/47609727

到此這篇關(guān)于MySQL中將一列以逗號(hào)分隔的值行轉(zhuǎn)列的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MYSQL 逗號(hào)分割行轉(zhuǎn)列內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

您可能感興趣的文章:
  • 當(dāng)Mysql行鎖遇到復(fù)合主鍵與多列索引詳解
  • Mysql 相鄰兩行記錄某列的差值方法
  • mysql指定某行或者某列的排序?qū)崿F(xiàn)方法
  • mysql 列轉(zhuǎn)行,合并字段的方法(必看)
  • mysql列轉(zhuǎn)行以及年月分組實(shí)例
  • mysql 列轉(zhuǎn)行的技巧(分享)
  • mysql 行轉(zhuǎn)列和列轉(zhuǎn)行實(shí)例詳解
  • mysql 行列動(dòng)態(tài)轉(zhuǎn)換的實(shí)現(xiàn)(列聯(lián)表,交叉表)
  • 數(shù)據(jù)庫實(shí)現(xiàn)行列轉(zhuǎn)換(mysql示例)
  • MySQL 中行轉(zhuǎn)列的方法

標(biāo)簽:天津 牡丹江 合肥 公主嶺 呼和浩特 阿里 沈陽 惠州

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL中將一列以逗號(hào)分隔的值行轉(zhuǎn)列的實(shí)現(xiàn)》,本文關(guān)鍵詞  MySQL,中將,一列,以,逗號(hào),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL中將一列以逗號(hào)分隔的值行轉(zhuǎn)列的實(shí)現(xiàn)》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySQL中將一列以逗號(hào)分隔的值行轉(zhuǎn)列的實(shí)現(xiàn)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章