遇到一个数据表遍历的问题,请高手指点,先在这里谢谢了!数据如下:
-----------------
ID content
1 8,10,12
2 3,2
3 12,14,17,19
4 0,9,23
-----------------
规则:
1.遍历上面数据中的content列(记录条数不确定);
2.以逗号分隔的数字如果大于10,则计数器加1。如8,10,12,则计数器为2;
3.对content列的处理结果与上表拼起来。最后的结果如下:
-----------------------
ID content Result
1 8,10,12 2
2 3,2 0
3 12,14,17,19 4
4 0,9,23 1
-----------------------
-----------------
ID content
1 8,10,12
2 3,2
3 12,14,17,19
4 0,9,23
-----------------
规则:
1.遍历上面数据中的content列(记录条数不确定);
2.以逗号分隔的数字如果大于10,则计数器加1。如8,10,12,则计数器为2;
3.对content列的处理结果与上表拼起来。最后的结果如下:
-----------------------
ID content Result
1 8,10,12 2
2 3,2 0
3 12,14,17,19 4
4 0,9,23 1
-----------------------
select *,result=2*len(replace(content,',',''))-len(content)-1
from tb
create table testReplace
(
Col1 int identity(1,1),
COl2 varchar(255)
)--drop table testReplaceinsert into testReplace
select '1112,23'
union select '1,10'
union select '3'select COl1,SUM(Col2) from
(select
a.COl1,CAST(b.Col2 as int) Col2,
(case when b.Col2>10 then 1 else 2 end ) as num
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from testReplace)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
) as ta group by COl1
create table tb(ID int,content varchar(20))
insert into tb select 1,'8,10,12'
insert into tb select 2,'3,2'
insert into tb select 3,'12,14,17,19'
insert into tb select 4,'0,9,23'
go
create function ft(@ct varchar(20),@flg int)
returns int
begin
declare @n int
set @n=0
set @ct=@ct+','
while len(@ct)>1
begin
if convert(int,left(@ct,charindex(',',@ct)-1))>=@flg
set @n=@n+1
set @ct=right(@ct,len(@ct)-charindex(',',@ct))
end
return @n
end
go
select *,dbo.ft(content,7) as [>7] from tb
/*
ID content >7
----------- -------------------- -----------
1 8,10,12 3
2 3,2 0
3 12,14,17,19 4
4 0,9,23 2(4 行受影响)*/
go
drop function dbo.ft
drop table tb