create table #t (a int ,b varchar(1000))
insert into #t
select 1,'1'
union
select 1,'2'
union
select 1,'3'
union
select 1,'4'
union
select 1,'5'
union
select 2,'6'
union
select 2,'7'
union
select 3,'8'select * from #t
如果a 相同,满足4个为一组
结果如下
a b
1 1;2;3;4
1 5
2 6;7
3 8
create table #t (a int ,b varchar(1000))
insert into #t
select 1,'1'
union
select 1,'2'
union
select 1,'3'
union
select 1,'4'
union
select 1,'5'
union
select 2,'6'
union
select 2,'7'
union
select 3,'8'declare @a int,@b varchar(8000),@bb varchar(8000)
update #t
set @bb = case when a = @a then @bb + ';' + b else b end
,@a = a,@b = b,b = @bbselect a,max(b)
from #t
group by adrop table #ta
----------- ---------------
1 1;2;3;4;5
2 6;7
3 8(所影响的行数为 3 行)
create table #t (a int ,b varchar(1000),i int)
insert into #t(a,b)
select 1,'1'
union
select 1,'2'
union
select 1,'3'
union
select 1,'4'
union
select 1,'5'
union
select 2,'6'
union
select 2,'7'
union
select 3,'8'declare @a int,@b varchar(8000),@bb varchar(8000),@i int
set @i = 0
update #t
set @i = case when a = @a and @i <= 4 then @i + 1 else 1 end
,@bb = case when a = @a and @i <= 4 then @bb + ';' + b else b end
,@a = a,@b = b,b = @bb,i = case when @i <= 4 then 0 else 1 endselect a,max(b)
from #t
group by a,i
order by a,idrop table #ta
----------- ----------
1 1;2;3;4
1 5
2 6;7
3 8(所影响的行数为 4 行)
学习一下!
declare @a int,@b varchar(8000),@bb varchar(8000),@i int,@ii int
select @i = 0,@ii = 0
update #t
set @i = case when a = @a and @i <= 4 then @i + 1 else 1 end
,@bb = case when a = @a and @i <= 4 then @bb + ';' + b else b end
,@ii = case when @i <= 4 and a = @a then @ii else @ii + 1 end
,@a = a,@b = b,b = @bb
,i = @ii
create table #t (a int ,b varchar(1000),i int)
insert into #t(a,b)
select 1,'1'
union
select 1,'2'
union
select 1,'3'
union
select 1,'4'
union
select 1,'5'
union
select 2,'6'
union
select 2,'7'
union
select 3,'8'declare @a int,@b varchar(8000),@bb varchar(8000),@i int,@ii int
select @i = 0,@ii = 0
update #t
set @i = case when a = @a and @i <= 4 then @i + 1 else 1 end
,@bb = case when a = @a and @i <= 4 then @bb + ';' + b else b end
,@ii = case when @i <= 4 and a = @a then @ii else @ii + 1 end
,@a = a,@b = b,b = @bb ,i = @iiselect a,max(b)
from #t
group by a,i
order by a,idrop table #ta
----------- ----------
1 1;2;3;4
1 5
2 6;7
3 8(所影响的行数为 4 行)
create table #t (a int ,b varchar(1000),ii int,i int)
insert into #t(a,b)
select 1,'1'
union
select 1,'2'
union
select 1,'3'
union
select 1,'4'
union
select 1,'5'
union
select 2,'6'
union
select 2,'7'
union
select 3,'8'declare @a int,@b varchar(8000),@bb varchar(8000),@i int,@ii int
select @i = 0,@ii = 0
update #t
set @bb = case when a = @a and @i < 2 then @bb + ';' + b else b end
,@ii = case when a = @a and @i < 2 then @ii else @ii+1 end
,@i = case when a = @a and @i < 2 then @i + 1 else 1 end
,@a = a,@b = b,b = @bb ,ii = @ii,i = @iselect a,max(b)
from #t
group by a,ii
order by a,iidrop table #t
a
----------- --------
1 1;2
1 3;4
1 5
2 6;7
3 8
a b ii i
----------- ---------- ----------- -----------
1 1 1 1
1 1;2 1 2
1 3 2 1
1 3;4 2 2
1 5 3 1
2 6 4 1
2 6;7 4 2
3 8 5 1