怎么将字符串
275-2-0-1001512-0-11.8000+275-2-1-1001512-1001370--+275-2-2-1001512-1001358--+275-3-0+1002316-0-48.000+275-3-1-1002316-1004365--等数据,
按照“+”分开这个需要循环,然后在按照“-”分开将每条数据每个字段截取出来插入到一个表,有些字段是空值就是--之间没数据,但也要分割只是插入的时候赋值个空,插入数据库后效果
表中的log,item字段数据是在sql中处理生成的,
就是说如果type字段为0item字段从0开始自增,log从1开始
下条数据如果type不等于0但foodid跟上条相等item就是1,log 也是上调的log值
如果type为0,如果foodid跟上条不相等item就要从0开始自增,log比上回大1
id log item foodid type price
275 2 0 1001512 0 11.8000
275 2 1 1001512 1001370
275 2 2 1001512 1001358
275 3 0 1002316 0 48.0000
275 3 1 1002316 1004365
整体说就是我怎样将字符串分割按照+,在按照-,插入到数据表,控制好log,和item 的值根据type
求一个函数?????
/*
标题:简单数据拆分(version 2.0)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2010-05-07
地点:重庆航天职业学院
描述:
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
*/
--1. 旧的解决方法(sql server 2000)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
--方法1.使用临时表完成
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','
DROP TABLE #
--方法2.如果数据量小,可不使用临时表
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number)
from tb a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.value)
where substring(',' + a.value , b.number , 1) = ','
--2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
--方法1.使用xml完成
SELECT A.id, B.value FROM
(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
) A OUTER APPLY
(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
) B
--方法2.使用CTE完成
;with tt as
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb
/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 行受影响)
*/
create table tb(value varchar(100))
insert into tb values('275-2-0-1001512-0-11.8000+275-2-1-1001512-1001370--+275-2-2-1001512-1001358--')
go
select a.number a_num , b.number b_num, value = substring(a.value , b.number , charindex('-' , a.value + '-' , b.number) - b.number)
from
(
select b.number,value = substring(a.value , b.number , charindex('+' , a.value + '+' , b.number) - b.number)
from tb a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.value)
where substring('+' + a.value , b.number , 1) = '+'
) a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.value)
where substring('-' + a.value , b.number , 1) = '-'
/*
a_num b_num value
----------- ----------- ----------------------------------------------------------------------------------------------------
1 1 275
1 5 2
1 7 0
1 9 1001512
1 17 0
1 19 11.8000
27 1 275
27 5 2
27 7 1
27 9 1001512
27 17 1001370
27 25
53 1 275
53 5 2
53 7 2
53 9 1001512
53 17 1001358
53 25
(所影响的行数为 18 行)
*/
select max(case px when 1 then value end) id,
max(case px when 2 then value end) [log],
max(case px when 3 then value end) [item],
max(case px when 4 then value end) [foodid],
max(case px when 5 then value end) [type],
max(case px when 6 then value end) [price]
from
(
select m.* , px = (select count(1) from
(
select a.number a_num , b.number b_num, value = substring(a.value , b.number , charindex('-' , a.value + '-' , b.number) - b.number)
from
(
select b.number,value = substring(a.value , b.number , charindex('+' , a.value + '+' , b.number) - b.number)
from tb a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.value)
where substring('+' + a.value , b.number , 1) = '+'
) a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.value)
where substring('-' + a.value , b.number , 1) = '-'
) n where n.a_num = m.a_num and n.b_num < m.b_num) + 1 from
(
select a.number a_num , b.number b_num, value = substring(a.value , b.number , charindex('-' , a.value + '-' , b.number) - b.number)
from
(
select b.number,value = substring(a.value , b.number , charindex('+' , a.value + '+' , b.number) - b.number)
from tb a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.value)
where substring('+' + a.value , b.number , 1) = '+'
) a join master..spt_values b
on b.type='p' and b.number between 1 and len(a.value)
where substring('-' + a.value , b.number , 1) = '-'
) m
) k
group by a_num
/*
id log item foodid type price
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
275 2 0 1001512 0 11.8000
275 2 1 1001512 1001370
275 2 2 1001512 1001358
(所影响的行数为 3 行)
*/
drop table tb