如把表A里的数据 按3个数为1组统计出现的次数 谢谢表aID 数据
1 12345612321232123212921233311232333333338
2 23211232123211321321321321212121212123211
3....得结果
ID 123 212
1 3次 2次
例如上面的
把1里的数据每隔3个划分为1组,统计出现123的次数 212
1 123 456 123 212 321 232 129 212 333 112 323 333 333 38 --------结尾处剩余1个或2个都算1组这样一共是14组数据.出现123的为3次.212为2次
1 12345612321232123212921233311232333333338
2 23211232123211321321321321212121212123211
3....得结果
ID 123 212
1 3次 2次
例如上面的
把1里的数据每隔3个划分为1组,统计出现123的次数 212
1 123 456 123 212 321 232 129 212 333 112 323 333 333 38 --------结尾处剩余1个或2个都算1组这样一共是14组数据.出现123的为3次.212为2次
insert into tb select 1,'12345612321232123212921233311232333333338'
insert into tb select 2,'23211232123211321321321321212121212123211'
go
declare @t table(code1 varchar(3))
insert into @t select substring(code,1,3) from tb union all
select substring(code,4,3) from tb union all
select substring(code,7,3) from tb union all
select substring(code,10,3) from tb union all
select substring(code,13,3) from tb union all
select substring(code,16,3) from tb union all
select substring(code,19,3) from tb union all
select substring(code,22,3) from tb union all
select substring(code,25,3) from tb union all
select substring(code,28,3) from tb union all
select substring(code,31,3) from tb union all
select substring(code,34,3) from tb union all
select substring(code,37,3) from tb union all
select substring(code,40,3) from tb
select code1,count(*) from @t group by code1
go
drop table tb
/*
code1
----- -----------
11 1
112 2
113 1
121 2
123 2
129 1
212 4
213 3
232 4
321 2
323 1
333 3
38 1
456 1
*/
declare c_data cursor for select a,data from tb
declare @tb table (a varchar(2),data varchar(30))
declare @i int,@j int,@count int
declare @a varchar(10),@data varchar(100)
open c_data
fetch c_data into @a,@data
while @@fetch_status=0
begin
select @j=1,@count=len(@data),@i=1
while @j<=@count
begin
if substring(@data,@j,3)='123'or substring(@data,@j,3)='212'
insert into @tb select @a,substring(@data,@j,3)
select @j=@j+3
end
fetch next from c_data into @a,@data
end
close c_data
deallocate c_data
select a,data,count(*) as 次数 from @tb group by a,data1 123 2
1 212 2
2 212 2
select id , 数据 , count(1) cnt from
(
select id , 数据 = substring(数据 , 1 , 3) from tb
union all
select id , 数据 = substring(数据 , 4 , 3) from tb
union all
select id , 数据 = substring(数据 , 7 , 3) from tb
...
) t
group by id , 数据