drop table #nums create table #nums(id int identity(1,1), n int)insert into #nums select 1 union all select 2 union all select 2 union all select 1 union all select 2 union all select 3 union all select 2 union all select 2 union all select 2 union all select 3 union all select 3 union all select 5 union all select 5 ; with cte as ( select *, 1 t from #nums where id = 1 union all select #nums.*, case when cte.n = #nums.n then cte.t else cte.t+1 end t from #nums inner join cte on #nums.id = cte.id + 1 ) select COUNT(1) from cte group by t/* result:(No column name) 1 2 1 1 1 3 2 2 */
加上 select COUNT(1) from cte group by t option(maxrecursion 0)
create table tb(haoma varchar(50)) insert into tb values('1114330000333') insert into tb values('1221232223355') gocreate function dbo.f_str(@haoma varchar(50)) returns varchar(50) as begin declare @str varchar(50) set @str = '' declare @s1 char(1) declare @s2 char(1) set @s1 = '' declare @i as int declare @j as int set @i = 1 set @j = 1 while @i <= len(@haoma) begin set @s2 = substring(@haoma , @i , 1) if @s1 <> @s2 begin set @s1 = @s2 if @i <> 1 set @str = @str + ltrim(@j) set @j = 1 end else set @j = @j + 1 if @i = len(@haoma) set @str = @str + ltrim(@j) set @i = @i + 1 end return @str end go--调用函数 select haoma , 统计结果 = dbo.f_str(haoma) from tbdrop function dbo.f_strdrop table tb/* haoma 统计结果 -------------------------------------------------- -------------------------------------------------- 1114330000333 31243 1221232223355 12111322(所影响的行数为 2 行)*/
如:haoma列为:
1 1 1 4 3 3 0 0 0 0 3 3 3不理解你的意思:相邻列,但又给出
如:haoma列为:
1 1 1 4 3 3 0 0 0 0 3 3 3这是一个列还是多个??说清楚点
create table #nums(id int identity(1,1), n int)insert into #nums
select 1 union all
select 2 union all
select 2 union all
select 1 union all
select 2 union all
select 3 union all
select 2 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 5 union all
select 5
;
with cte as
(
select *, 1 t from #nums
where id = 1
union all
select #nums.*, case when cte.n = #nums.n then cte.t else cte.t+1 end t from #nums
inner join cte
on #nums.id = cte.id + 1
)
select COUNT(1) from cte
group by t/*
result:(No column name)
1
2
1
1
1
3
2
2
*/
加上
select COUNT(1) from cte
group by t
option(maxrecursion 0)
insert into tb values('1114330000333')
insert into tb values('1221232223355')
gocreate function dbo.f_str(@haoma varchar(50)) returns varchar(50)
as
begin
declare @str varchar(50)
set @str = ''
declare @s1 char(1)
declare @s2 char(1)
set @s1 = ''
declare @i as int
declare @j as int
set @i = 1
set @j = 1
while @i <= len(@haoma)
begin
set @s2 = substring(@haoma , @i , 1)
if @s1 <> @s2
begin
set @s1 = @s2
if @i <> 1
set @str = @str + ltrim(@j)
set @j = 1
end
else
set @j = @j + 1
if @i = len(@haoma)
set @str = @str + ltrim(@j)
set @i = @i + 1
end
return @str
end
go--调用函数
select haoma , 统计结果 = dbo.f_str(haoma) from tbdrop function dbo.f_strdrop table tb/*
haoma 统计结果
-------------------------------------------------- --------------------------------------------------
1114330000333 31243
1221232223355 12111322(所影响的行数为 2 行)*/