在数据存储数据库中,常常存在信息以特殊符号隔开形式存储在一个字段里,如下表:
当我们需要把“去过的城市”字段以逗号(,)拆分成多行时,如下表:
这时就会涉及到列转行,下面跟着我的例子,一起来见证奇迹吧:
创建表
CREATE TABLE userinfomation (id number(2) NOT NULL,username varchar(200) DEFAULT NULL,city varchar(500) DEFAULT NULL)
插入测试数据:
insert into userinfomation values(1,'张三','云南、北京、上海');insert into userinfomation values(2,'李四','西安、重庆、成都');insert into userinfomation values(3,'小花','甘肃、深圳');
查询sql
将代码拷贝使用时,需要更改表名、字段名、分隔字符等(注:使用的时候还需将将N替换为分隔符的长度,如分割符“、”,长度位1,则将N替换位1,一共4处)。
SELECT id,substr(a.city, instr(a.city, '、', 1, levels.lvl) + N, instr(a.city, '、', 1, levels.lvl + 1) -(instr(a.city, '、', 1, levels.lvl) + 1)-(N-1)) as city,usernameFROM(SELECT id,'、' || city || '、' AS city,username,(length(city || '、') - nvl(length(REPLACE(city, '、')), 0))/N AS cntFROM userinfomation) a,(SELECT rownum AS lvlFROM (SELECT (MAX(length(city || '、') - nvl(length(REPLACE(city, '、')), 0)))/N max_len FROM userinfomation)CONNECT BY LEVEL <= max_len) levelsWHERE levels.lvl <= tORDER BY id,levels.lvl;
执行结果
可以从上截图看出,已经将userinfomation表的city 字段根据“、”号分割后,形成多行数据。赶快去试试吧。有问题欢迎评论区留言哦。
借鉴原文来至:
/qq_20481125/article/details/99550208, /su-root/p/15008584.html