如把表A里的数据 按3个数为1组统计出现的次数 谢谢 表a ID 数据
1 12345612321232123212921233311232333333338
2 23211232123211321321321321212121212123211
3.... 得结果
ID 123 212
1 2次 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的为2次.212为2次得结果
ID 123 212
1 2次 2次
1 12345612321232123212921233311232333333338
2 23211232123211321321321321212121212123211
3.... 得结果
ID 123 212
1 2次 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的为2次.212为2次得结果
ID 123 212
1 2次 2次
--测试数据
declare @t table (fid int,数据 varchar(200))
insert @t
select 1,'12345612321232123212921233311232333333338' union
select 2,'23211232123211321321321321212121212123211'--查询
select top 100 fid=identity(int,0,1) into #t from dbo.syscolumns,dbo.sysobjects
select fid,
'123'=sum(case when col='123' then 1 else 0 end),
'212'=sum(case when col='212' then 1 else 0 end)
from (
select a.fid,substring(数据,b.fid*3+1,3) col from @t a,#t b
where substring(数据,b.fid*3+1,3) in ('123','212')
) a group by fid order by fiddrop table #t/*结果
1 2 2
2 0 2
*/
--> --> (让你望见影子的墙)生成測試數據
if not object_id('tb') is null
drop table tb
Go
Create table tb(a int,data varchar(100))
Insert tb
select 1,'12345612321232123212921233311232333333338' union all
select 2,'23211232123211321321321321212121212123211'
Go
Select * from tbdeclare 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
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_dataselect a,data123=isnull(max( case data when 123 then 次数 end ),0),data212=isnull(max( case data when 212 then 次数 end),0)
from ( select a,data,count(*) as 次数 from @tb group by a,data )Ka data123 data212
1 2 2
2 0 2
再做一次行列转换不就行了?
INSERT INTO test88 SELECT 1 ,'12345612321232123212921233311232333333338'
UNION ALL SELECT 2 ,'23211232123211321321321321212121212123211' CREATE TABLE test89(id int,b varchar(20))DECLARE @a table(a int identity(0,1),b int)
INSERT @a SELECT TOP 200 0 FROM syscolumns sINSERT test89
SELECT id, substring(data,3*a+1,3) aa
FROM test88 a,@a b WHERE substring(data,3*a+1,3)<>''DECLARE @sql varchar(8000)
SELECT @sql='select id'
SELECT @sql=isnull(@sql+',','')+'sum(case when b='''+b +''' then 1 else 0 end) ['+b+']' FROM test89 GROUP BY b
SELECT @sql
exec(@sql+' from test89 group by id')--result
/*
id 11 112 113 121 123 129 212 213 232 321 323 333 38 456
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 0 1 0 0 2 1 2 0 1 1 1 3 1 1
2 1 1 1 2 0 0 2 3 3 1 0 0 0 0 */