求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;
