select 编号,sum(数量) 总数量 from ( SELECT A.编号, B.数量 FROM( SELECT 编号, 数量 = CONVERT(xml,'<root><v>' + REPLACE(数量, ',', '</v><v>') + '</v></root>') FROM tb )A OUTER APPLY( SELECT value = N.v.数量('.', 'varchar(100)') FROM A.数量.nodes('/root/v') N(v) )B ) C
create table tb(no int,num varchar(20)) insert into tb select 1,'40,30,22' union select 3,'41,30,32' select a.no, sum(substring(a.num,b.number,charindex(',',a.num+',',b.number)-b.number)*1) as num from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(a.num) and substring(','+a.num,b.number,1) = ',' group by a.no /* no num ----------- ----------- 1 92 3 103(2 行受影响)
select 编号,sum(convert(数量,int) 总数量 from ( SELECT A.编号, B.数量 FROM( SELECT 编号, 数量 = CONVERT(xml,'<root><v>' + REPLACE(数量, ',', '</v><v>') + '</v></root>') FROM tb )A OUTER APPLY( SELECT value = N.v.数量('.', 'varchar(100)') FROM A.数量.nodes('/root/v') N(v) )B ) C
create table tb(id int,val varchar(40)) insert into tb select 1,'40,30,22' godeclare @sql varchar(4000) select @sql = isnull(@sql+' union all ','')+' select id,'+replace(val,',','+')+' as val from tb where id = '+ltrim(id) from tb exec(@sql)drop table tb/***************1 92
create table tb(id int,val varchar(40)) insert into tb select 1,'40,30,22' union all select 3,'41,30,32' union all select 4,'50,35,22' union all select 7,'32,23,1' union all select 9,'12,45,2'godeclare @sql varchar(4000) select @sql = isnull(@sql+' union all ','')+' select id,' +replace(val,',','+')+' as val from tb where id = '+ltrim(id) from tb exec(@sql)drop table tb /**************id val ----------- ----------- 1 92 3 103 4 107 7 56 9 59(5 行受影响)
select 编号,sum(cast(数量 as int)) 总数量 from ( SELECT A.编号, B.数量 FROM( SELECT 编号, 数量 = CONVERT(xml,'<root><v>' + REPLACE(数量, ',', '</v><v>') + '</v></root>') FROM tb )A OUTER APPLY( SELECT 数量 = N.v.value('.', 'varchar(100)') FROM A.数量.nodes('/root/v') N(v) )B ) C group by 编号
色狼是按,分组楼主可以这样看看:select a.no, substring(a.num,b.number,charindex(',',a.num+',',b.number)-b.number) as num from tb a,master..spt_values b where b.[type] = 'p' and b.number between 1 and len(a.num) and substring(','+a.num,b.number,1) = ','
select 编号,sum(数量) as 总数量 from tb group by 编号
(
SELECT A.编号, B.数量
FROM(
SELECT 编号, 数量 = CONVERT(xml,'<root><v>' + REPLACE(数量, ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.数量('.', 'varchar(100)') FROM A.数量.nodes('/root/v') N(v)
)B
) C
create table tb(no int,num varchar(20))
insert into tb
select 1,'40,30,22' union
select 3,'41,30,32'
select a.no,
sum(substring(a.num,b.number,charindex(',',a.num+',',b.number)-b.number)*1) as num
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.num)
and substring(','+a.num,b.number,1) = ','
group by a.no /*
no num
----------- -----------
1 92
3 103(2 行受影响)
(
SELECT A.编号, B.数量
FROM(
SELECT 编号, 数量 = CONVERT(xml,'<root><v>' + REPLACE(数量, ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.数量('.', 'varchar(100)') FROM A.数量.nodes('/root/v') N(v)
)B
) C
create table tb(id int,val varchar(40))
insert into tb
select 1,'40,30,22'
godeclare @sql varchar(4000)
select @sql = isnull(@sql+' union all ','')+' select id,'+replace(val,',','+')+' as val from tb where id = '+ltrim(id)
from tb
exec(@sql)drop table tb/***************1 92
create table tb(id int,val varchar(40))
insert into tb
select 1,'40,30,22' union all
select 3,'41,30,32' union all
select 4,'50,35,22' union all
select 7,'32,23,1' union all
select 9,'12,45,2'godeclare @sql varchar(4000)
select @sql = isnull(@sql+' union all ','')+' select id,'
+replace(val,',','+')+' as val from tb where id = '+ltrim(id)
from tb
exec(@sql)drop table tb
/**************id val
----------- -----------
1 92
3 103
4 107
7 56
9 59(5 行受影响)
(
SELECT A.编号, B.数量
FROM(
SELECT 编号, 数量 = CONVERT(xml,'<root><v>' + REPLACE(数量, ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT 数量 = N.v.value('.', 'varchar(100)') FROM A.数量.nodes('/root/v') N(v)
)B
) C
group by 编号
substring(a.num,b.number,charindex(',',a.num+',',b.number)-b.number) as num
from tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.num)
and substring(','+a.num,b.number,1) = ','