1.創(chuàng)建表的示例
CREATE TABLE tbl_name(
id int(11) not null auto_increment,
userName varchar(100) not null,
PRIMARY KEY(id)
)
ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
insert into tbl_name values (1,'a,aa,aaa');
insert into tbl_name values (2,'b,bb');
insert into tbl_name values (3,'c,cc')
數(shù)據(jù)如下圖:
SELECT a.id,SUBSTRING_INDEX(SUBSTRING_INDEX(a.userName,‘,’,b.help_topic_id+1),‘,’,-1) as name
from tbl_name a left join mysql.help_topic b
on b.help_topic_id < (LENGTH(a.userName)-LENGTH(REPLACE(a.userName,‘,’,‘’))+1)
ORDER BY a.id;
本人需求為 圖層樹的父級節(jié)點都在 ancestors 字段中,需要獲取多個圖層中信息的不重復(fù)的父級節(jié)點即可使用
DROP TABLE IF EXISTS `map_two_layer`;
CREATE TABLE `map_two_layer` (
`layer_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '圖層ID',
`node_types` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '節(jié)點類型（0目錄1服務(wù)）',
`parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父級ID',
`ancestors` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '祖級列表'
PRIMARY KEY (`layer_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1732 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '圖層注冊' ROW_FORMAT = DYNAMIC;
SET FOREIGN_KEY_CHECKS = 1;
主要表結(jié)構(gòu)如下:
文章來源：http://www.zghlxwxcb.cn/news/detail-505482.html
SELECT DISTINCT (SUBSTRING_INDEX(SUBSTRING_INDEX(a.ancestors,',',b.help_topic_id+1),',',-1) ) as name
from map_two_layer a left join mysql.help_topic b
on b.help_topic_id < (LENGTH(a.ancestors)-LENGTH(REPLACE(a.ancestors,',',''))+1) ORDER BY a.layer_id ;
查詢后對主鍵ID去重即可獲取到需要的相關(guān)信息
文章來源地址http://www.zghlxwxcb.cn/news/detail-505482.html
到了這里，關(guān)于MYSQL： sql中某一個字段內(nèi)容為用逗號分割的字符串轉(zhuǎn)換成多條數(shù)據(jù)(適用于部分樹機構(gòu))的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
