求SQL语句,要求取出的数据行数为8的倍数
1.要求取出的数据行数为8的倍数
2.当数据行数小于8的时候,自动添加补齐为8行
3.当数据行数大于8的时候,自动添加补齐为8的倍数行

例如,tabA里有一个列colA,数据为
1,2,5,7   (4行数据),补齐后得到数据为
1,2,5,7,8,9,10,11,以最后一行的数为基础向上累加



------解决方案--------------------
这里是用2005生成测试数据方法相同:
select top 5 id=row_number() over (order by id)
into #
from sysobjects

declare @i int
select @i=max(id) from #
while(select count(*) from #)%8!=0
begin
insert # values(@i+1)
set @i=@i+1
end
select * from # order by id
id
--------------------
1
2
3
4
5
6
7
8

(8 行受影响)


select top 9 id=row_number() over (order by id)
into #
from sysobjects

declare @i int
select @i=max(id) from #
while(select count(*) from #)%8!=0
begin
insert # values(@i+1)
set @i=@i+1
end
select * from # order by id

drop table #

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)

(1 行受影响)
id
--------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

(16 行受影响)



------解决方案--------------------
--:-) 8的倍数
DECLARE @COUNT INT
SELECT COUNT = COUNT(1) FROM tableA


WHILE (@COUNT%8 <> 0 ) AND (@COUNT%8 < 8) DO BEGIN
INSERT INTO tableA(colA) SELECT MAX(colA) + 1 FROM tableA
SET @COUNT = @COUNT + 1
END;