sql循环累加数据问题，求大婶解决方法或思路
ID CZ
----------------------------
628100|5000|2000|3000
7910000|1|1
9710000
10311
1041|999
120100
12267|68|58
12420|25
12533|1|1
数据表是类似这样的，我希望得到CZ的所有累计和，但是CZ列中的数据都是通过| 隔开的，我不知道怎么循环取总和数据。
[解决办法]
示例，自行修改
set @sql=concat('select id,',replce(CZ,'
[解决办法]
','+'),' from tt');
prepare stml from @sql;
execuste stml;
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-04 13:20:59
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[CZ] varchar(19))
insert [huang]
select 62,'8100
[解决办法]
5000
[解决办法]
2000
[解决办法]
3000' union all
select 79,'10000
[解决办法]
1
[解决办法]
1' union all
select 97,'10000' union all
select 103,'11' union all
select 104,'1
[解决办法]
999' union all
select 120,'100' union all
select 122,'67
[解决办法]
68
[解决办法]
58' union all
select 124,'20
[解决办法]
25' union all
select 125,'33
[解决办法]
1
[解决办法]
1'
--------------开始查询--------------------------
SELECT id,SUM([key]) [CZ]
FROM (
select
id,
CONVERT(INT,SUBSTRING([CZ],number,CHARINDEX('
[解决办法]
',[CZ]+'
[解决办法]
',number)-number) )as [key]
from
[huang] a,master..spt_values
where
number >=1 and number<=len([CZ])
and type='p'
and substring('
[解决办法]
'+[CZ],number,1)='
[解决办法]
')a
GROUP BY id
----------------结果----------------------------
/*
id CZ
----------- -----------
62 18100
79 10002
97 10000
103 11
104 1000
120 100
122 193
124 45
125 35
*/
