楼主好像没有表达清楚,猜一个:
select top 25 id=identity(int,1,1),con=1
into #--生成临时表
from sysobjectsselect id,con=case when id%8>0 then id/8+1 else id/8 end
from #
id con
----------- -----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 2
10 2
11 2
12 2
13 2
14 2
15 2
16 2
17 3
18 3
19 3
20 3
21 3
22 3
23 3
24 3
25 4(25 行受影响)
select top 25 id=identity(int,1,1),con=1
into #--生成临时表
from sysobjectsselect id,con=case when id%8>0 then id/8+1 else id/8 end
from #
id con
----------- -----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 2
10 2
11 2
12 2
13 2
14 2
15 2
16 2
17 3
18 3
19 3
20 3
21 3
22 3
23 3
24 3
25 4(25 行受影响)
有个表 tabA ,里面有个列 colA,列内数据如下
colA
---------
1
2
4
5
共4行数据
要求Select出来行数为8行,或8的倍数
colA
---------
1
2
4
5
6 <---从这里开始为自动补充的行,内容接上一行的内容加1
7
8
9
--------------------------------------------------如果有9行数据,也要自动补充为16行,即8的倍数行不知道这样表达清楚没?
SELECT COUNT(1) FROM tableAWHILE @COUNT < 8 DO BEGIN
INSERT INTO tableA(colA) SELECT MAX(colA) + 1 FROM tableA
SET @COUNT = @COUNT + 1
END;
DECLARE @COUNT INT
SELECT COUNT = COUNT(1) FROM tableAWHILE @COUNT < 8 DO BEGIN
INSERT INTO tableA(colA) SELECT MAX(colA) + 1 FROM tableA
SET @COUNT = @COUNT + 1
END;
select top 5 id=row_number() over (order by id)
into #
from sysobjectsdeclare @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 sysobjectsdeclare @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 iddrop 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 行受影响)
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;