表 ATTACHMENT 字段 A_ID , A_NAME(这个字段需要分多列),想把这些数据分开插入到新表 ATTA_NEW 中,如下:当前 ATTACHMENT 表内容如下:
A_ID A_NAME
1 AA*BB*CC
2 DD
3 BB*EE
4 FF
5 FF*CC需要实现的结果,ATTA_NEW 表,AN_ID 是递增的:
ATTA_NEW 表中 A_ID , A_NAME 的值分别是 ATTACHMENT 表中A_ID ,A_NAME 的值需要的结果如下:
AN_ID A_ID A_NAME
1 1 AA
2 1 BB
3 1 CC
4 2 DD
5 3 BB
6 3 EE
7 4 FF
8 5 FF
9 5 CC
有大大能指导一下吗?
A_ID A_NAME
1 AA*BB*CC
2 DD
3 BB*EE
4 FF
5 FF*CC需要实现的结果,ATTA_NEW 表,AN_ID 是递增的:
ATTA_NEW 表中 A_ID , A_NAME 的值分别是 ATTACHMENT 表中A_ID ,A_NAME 的值需要的结果如下:
AN_ID A_ID A_NAME
1 1 AA
2 1 BB
3 1 CC
4 2 DD
5 3 BB
6 3 EE
7 4 FF
8 5 FF
9 5 CC
有大大能指导一下吗?
if object_id('ATTACHMENT') is not null drop table ATTACHMENT
create table ATTACHMENT(A_ID int, A_NAME varchar(8))
insert into ATTACHMENT
select 1, 'AA*BB*CC' union all
select 2, 'DD' union all
select 3, 'BB*EE' union all
select 4, 'FF' union all
select 5, 'FF*CC'--> 数字辅助表拆分
if object_id('tempdb.dbo.#Nums') is not null drop table #Nums
select top 8 n = identity(int,1,1) into #Nums from syscolumns --> top --> top (待拆分列的最大字符个数)
select a.A_ID, A_NAME = substring(a.A_NAME+'*', b.n, charindex('*', a.A_NAME+'*', b.n+1) - b.n) from ATTACHMENT a, #Nums b where substring('*'+a.A_NAME, b.n, 1) = '*'
/*
A_ID A_NAME
-------- --------
1 AA
1 BB
1 CC
2 DD
3 BB
3 EE
4 FF
5 FF
5 CC
*/
;
--> CTE 拆分 (SQL2005)
with Pos (A_ID, P1, P2) as
(
select A_ID, charindex('*', '*'+A_NAME), charindex('*', A_NAME+'*') + 1 from ATTACHMENT
union all
select a.A_ID, b.P2, charindex('*', A_NAME+'*', b.P2) + 1 from ATTACHMENT a join Pos b on a.A_ID = b.A_ID where charindex('*', A_NAME+'*', b.P2) > 0
)
select a.A_ID, A_NAME = substring(a.A_NAME+'*', b.P1, b.P2-b.P1-1) from ATTACHMENT a join Pos b on a.A_ID = b.A_ID order by a.A_ID option (maxrecursion 0)--> 删除测试:
drop table ATTACHMENT, #Nums
create table #ATTACHMENT( A_ID int ,A_NAME varchar(50))
insert #ATTACHMENT select 1 ,'AA*BB*CC'
insert #ATTACHMENT select 2 ,'DD'
insert #ATTACHMENT select 3 ,'BB*EE'
insert #ATTACHMENT select 4 ,'FF'
insert #ATTACHMENT select 5 ,'FF*CC'select row_number() over(order by getdate()) as AN_ID_new,a.A_ID,c2.value('.','varchar(20)') as A_NAME
from #ATTACHMENT a
cross apply (select xmlcode=CAST('<row>'+replace(A_NAME,'*','</row><row>')+'</row>' as xml)) c1
cross apply xmlcode.nodes('*') t(c2)
AN_ID_new A_ID A_NAME
-------------------- ----------- --------------------
1 1 AA
2 1 BB
3 1 CC
4 2 DD
5 3 BB
6 3 EE
7 4 FF
8 5 FF
9 5 CC(9 行受影响)
if object_id('[tb]') is not null drop table [tb]
create table [tb]([A_ID] int,[A_NAME] varchar(8))
insert [tb]
select 1,'AA*BB*CC' union all
select 2,'DD' union all
select 3,'BB*EE' union all
select 4,'FF' union all
select 5,'FF*CC'--------2000/05均可用
SELECT a.A_ID, A_NAME=SUBSTRING(a.A_NAME,number,CHARINDEX('*',a.A_NAME+'*',number)-b.number)FROM tb a JOIN master..spt_values b ON b.type='p' --AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可 AND CHARINDEX('*','*'+a.A_NAME,number)=number
/*
A_ID A_NAME
----------- --------
1 AA
1 BB
1 CC
2 DD
3 BB
3 EE
4 FF
5 FF
5 CC(所影响的行数为 9 行)*/