sql2000 求累计数出现次数,谢谢表1
id 数据
1 bebbbebbebbeeeeebbeeeebebbbb
2 ebbbeeeebbebbeeeeebbeeeebebe
............
把b看为1 e为-1 然后累计相加，求相加后个数出现的次数。例如上面
1 bebbbebbebbeeeeebbeeeebebbbbb 1 1
e -1 0
b 1 1
b 1 2
b 1 3
e -1 2
b 1 3
b 1 4
e -1 3
b 1 4
b 1 5
e -1 4
e -1 3
e -1 2
e -1 1
e -1 0
b 1
b 1
e -1
e -1
e -1
e -1
b 1
e -1
b 1
b 1
b 1
b 1
得结果id 0 1 2 3 4
1 2次 3次 3次
2.............
id 0 1 2 3 4 这个样子行么？declare @a varchar(8000)
declare @Col2 int
declare @Col3 int
declare @testtable table(Col1 char(1),Col2 int,Col3 int)set @a = 'bebbbebbebbeeeeebbeeeebebbbb'
set @Col3 = 0
while(left(@a,1)<>'')
begin
if left(@a,1) = 'b'
set @Col2 = 1
else
set @Col2 = -1
set @Col3 = @Col3 +@Col2
insert into @testtable values(left(@a,1),@Col2,@Col3)
set @a = right(@a,len(@a)-1)
endselect distinct Col3,cast(count(Col3) as varchar(20))+'次' as '次数' from @testtable group by Col3Col3 次数
-2 2次
-1 3次
0 4次
1 6次
2 5次
3 4次
4 3次
5 1次
--------------------
(8 件処理されました)
insert into tb select 1,'bebbbebbebbeeeeebbeeeebebbbb'
insert into tb select 2,'ebbbeeeebbebbeeeeebbeeeebebe'
go
create function getsame(@col1 varchar(100))returns int
as
begin
declare @i int,@j int
set @i=0
set @j=0
while @i<len(@col1)
begin
set @j=@j+(case when substring(@col1,@i+1,1)='b' then 1 else -1 end)
set @i=@i+1
end
return @j
end
go
select id,dbo.getsame(col1) from tb
go
drop table tb
drop function dbo.getsame
/*
id
----------- -----------
1 2
2 -6
*/
id int,
数据 varchar(100)
)
goinsert 表1 select
1, 'bebbbebbebbeeeeebbeeeebebbbb'
union all select
2, 'ebbbeeeebbebbeeeeebbeeeebebe'godeclare @sql varchar(8000)
set @sql=''select @sql=@sql+',sum(case when x='+cast(x as varchar)+' then 1 else 0 end) as ['+cast(x as varchar)+']'
from (
select c.id
,(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),'b','')))-(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),'e',''))) as x
,count(1) as y
from (
select 1 as a
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0
) as a,(
select 1 as b
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0
) as b,表1 c
where a.a+10*b.b < len(c.数据)
group by c.id
,(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),'b','')))-(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),'e','')))
) as t
group by x
exec('select id'+@sql +'
from (
select c.id
,(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),''b'','''')))-(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),''e'',''''))) as x
,count(1) as y
from (
select 1 as a
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0
) as a,(
select 1 as b
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0
) as b,表1 c
where a.a+10*b.b < len(c.数据)
group by c.id
,(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),''b'','''')))-(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),''e'','''')))
) as t
group by id
')------结果
id -6 -5 -4 -3 -2 -1 0 1 2 3 4 5
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 0 0 0 0 1 1 1 1 1 1 1 1
2 1 1 1 1 1 1 1 1 1 0 0 0
id int,
数据 varchar(100)
)
goinsert 表1 select
1, 'bebbbebbebbeeeeebbeeeebebbbb'
union all select
2, 'ebbbeeeebbebbeeeeebbeeeebebe'godeclare @sql varchar(8000)
set @sql=''select @sql=@sql+',sum(case when x='+cast(x as varchar)+' then y else 0 end) as ['+cast(x as varchar)+']'
from (
select c.id
,(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),'b','')))-(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),'e',''))) as x
,count(1) as y
from (
select 1 as a
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0
) as a,(
select 1 as b
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0
) as b,表1 c
where a.a+10*b.b < len(c.数据)
group by c.id
,(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),'b','')))-(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),'e','')))
) as t
group by x
exec('select id'+@sql +'
from (
select c.id
,(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),''b'','''')))-(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),''e'',''''))) as x
,count(1) as y
from (
select 1 as a
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0
) as a,(
select 1 as b
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 0
) as b,表1 c
where a.a+10*b.b < len(c.数据)
group by c.id
,(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),''b'','''')))-(a.a+10*b.b+1-len(replace(left(c.数据,a.a+10*b.b+1),''e'','''')))
) as t
group by id
')------结果
id -6 -5 -4 -3 -2 -1 0 1 2 3 4 5
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 0 0 0 0 2 3 4 6 5 4 3 1
2 3 3 2 3 3 5 5 3 1 0 0 0