源表如下:VID NAME NUM
1 1AAA 100
1 1BBB 110
2 2AAA 200
2 2BBB 210
3 3CCC 300
3 3AAA 310
3 3BBB 320
3 3CCC 330想得到下面的结果。就是为上面的针对VID插入空行,让每个VID都拥有3的整数倍个行。VID NAME NUM
1 1AAA 100
1 1BBB 110
1 ???? 0
2 2AAA 200
2 2BBB 210
2 ???? 0
3 3CCC 300
3 3AAA 310
3 3BBB 320
3 3CCC 330
3 ???? 0
3 ???? 0针对每一个VID,不足3行的插入空行。多过3行的,则插空行成3的倍数。谢谢。
1 1AAA 100
1 1BBB 110
2 2AAA 200
2 2BBB 210
3 3CCC 300
3 3AAA 310
3 3BBB 320
3 3CCC 330想得到下面的结果。就是为上面的针对VID插入空行,让每个VID都拥有3的整数倍个行。VID NAME NUM
1 1AAA 100
1 1BBB 110
1 ???? 0
2 2AAA 200
2 2BBB 210
2 ???? 0
3 3CCC 300
3 3AAA 310
3 3BBB 320
3 3CCC 330
3 ???? 0
3 ???? 0针对每一个VID,不足3行的插入空行。多过3行的,则插空行成3的倍数。谢谢。
(
vid int,
name char(4),
num int
)
insert into @test
select 1,'1AAA',100 union all
select 1,'1BBB',110 union all
select 2,'2AAA',200 union all
select 2,'2BBB',210 union all
select 3,'3AAA',300 union all
select 3,'3BBB',310 union all
select 3,'3CCC',320 union all
select 3,'3DDD',330
declare @count int
select @count=1
while @count<3
begin
insert into @test
select vid,'????',0
from @test
group by vid
having count(*)%3!=0
set @count=@count+1
end
select * from @test order by vid,name