工作中遇到过,开发人员在设计数据库表时,为了方便,设计了不满足第一范式的数据表,但是生产分析需要数据时,又必须将违反范式要求的某一字段再次拆分分成多行多列的情况。在此我记录一下解决方法,举例仅供参考。

1、建表(不满足范式要求)

create table classinfo
studengt_class int ,
student   varchar(255) 

2、插入数据
 

insert into classinfo(studengt_class,student)  values(1,'101,张三,女,优;102,李四,男,优;103,王二,女,良;104,赵大,男,良');
insert into classinfo(studengt_class,student)  values(2,'201,张良,男,优;202,李华,女,优;203,王华,男,良;204,林黛玉,女,良');

3、看初始数据情况
 

select * from classinfo

该表仅两个字段,studengt_class,student,其中student字段将对应班级的所有学生信息都包括进去了。观察可以发现每一位学生都用分号;隔开,而每一位学生的学生信息包括 学号,姓名,性别,成绩表现。这几个字段又由逗号隔开。所以我们需要先按分号将每一位学生对象拆分出来成行,然后再按逗号拆分出学号,姓名,性别,成绩表现成列。

5、按分号拆分学生对象为多行

select studengt_class, 'student'=substring(a.student,b.number,charindex(';',a.student+';',b.number)-b.number)
from  classinfo  a
inner join master.dbo.spt_values b on b.number between 1 and len(a.student)
and substring(';'+a.student,b.number,1)=';'
 where b.type='P' 

 6、按逗号拆分学生对象的学生信息为多列

a、建立存储过程

creat FUNCTION [dbo].[f_GetStr](
@s varchar(8000), --要区分的字段
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(1000)
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,' ')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),' '))

b、使用存储过程f_GetStr  按逗号拆分学生对象的信息为多列

select studengt_class,
dbo.f_getstr(student,1,',') as student_number ,
dbo.f_getstr(student,2,',') as student_name,
dbo.f_getstr(student,3,',') as student_sex,
dbo.f_getstr(student,4,',') as student_grade 
(select studengt_class, 'student'=substring(a.student,b.number,charindex(';',a.student+';',b.number)-b.number)
  from  classinfo  a
  inner join master.dbo.spt_values b on b.number between 1 and len(a.student)
                                        and substring(';'+a.student,b.number,1)=';'
  where b.type='P' 

最终结果如下:

8、 总结:总共有两步,平时我们可能会遇到按分隔符只拆成多行或者多列的情况,那么我们只需要视情况采用关键两步中的一种即可解决问题:

(1、先按分隔符拆成多行数据

通用代码:master.dbo.spt_values 表为数据库系统自带表

select 表中的其他字段, '此处随便命名'=substring(需要拆分的字段名,b.number,charindex('分隔符',需要拆分的字段名+'分隔符',b.number)-b.number)
from  需要拆分字段的数据表名 a
inner join master.dbo.spt_values b on b.number between 1 and len(需要拆分的字段名)
and substring('分隔符'+需要拆分的字段名,b.number,1)='分隔符'
 where b.type='P' 

(2、建存储过程,利用存储过程函数,来按分隔符拆分为多列数据。

存储过程函数见6-a

9、初始与结果对比

select max(case when F1%3=1 then F1 else 0 end) a, max(case when F1%3=2 then F1 else 0 end) b, max(case when F1%3=0 then F1 else 0 end) c from HLR151 group by (F1-1)/3 select c1=a.F1,c2=b.F1,c3=c.F1 from HLR151 a left join HLR151 b on b.F1=a. TEST_TABLE.ID, TEST_TABLE.NAME, SUBSTRING(TEST_TABLE.ADDRESS,NUMBER,CHARINDEX(',',TEST_TABLE.ADDRESS+',',NUMBER)-NUMBER) AS ADDRESS FROM TEST_TABLE,MASTER..SPT_VALUES WHERE NUMBER >= 1 AND NUMBER < LEN(TEST_TABLE.ADDRESS) AND TYPE='P' 利用ORACLE的REGEXP_SUBSTR()进行处理就好了 SELECT DISTINCT REGEXP_SUBSTR( '371,370,368,409,1', '[^,]+', 1, LEVEL ) VERI_ITEM DUAL T CONNECT BY LEVEL <= LENGTH('371,370,368,409,1') - LENGTH( REPLACE ( '371,370,368,409,1', ',', '' SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num mysql.help... REGEXP_SUBSTR函数格式如下: function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) __srcstr :需要进行正则处理的字符串 __pattern :进行匹配的正则表达式 __position :起始位置,从第几个字符开始正则表达式匹配(默认为 1、master.dbo.spt_values 是一个数据库的常量表,常用来辅助写存储过程或者函数。 2、charindex函数; 3、另外不太懂得这段SQL的同学,可以把每个字段或者函数另起字段列拆分出来看。 4、还有inner join 中的substring(';'+a.student,b.number,1)=';' 这里就是关键条件,是定位分割符位置的(b.number)。也可以放到where 语句里面。 SQL SERVER 中将数据表中的字段按分隔符分成多行多列 ID13813770657: b表是什么啊 SQL SERVER 中将数据表中的字段按分隔符分成多行多列 Louis2021: 高手,请问inner join master.dbo.spt_values这张表长什么样子?对于inner join这部分不是很理解