--源表A
create table A
(
ID int primary key,
value int not null
)
--源表中数据
insert into A
select 1,11 union
select 2,12 union
select 3,13 union
select 4,14 union
select 5,15 union
select 6,16 union
select 7,17 union
select 8,18 union
select 9,19 union
select 10,20 union
select 11,21 union
select 12,22
GO
--目标表B
create table B
(
ID int primary key,
COM char(4)
)
GO
-------------------------------------------------
declare @COM int,@Count int,@ID int
declare
@SumOfPort int, --端口总数
@MaxOfPort int --每口最多几条
set @COM=1
set @Count=1
set @SumOfPort=3 --这里测试用3个端口
set @MaxOfPort=2 --这里测试用每口最多2条
declare abc cursor for
select ID from A
open abc
fetch next from abc into @ID
while @@FETCH_STATUS=0
begin
while @Count<=@SumOfPort*@MaxOfPort
begin
insert into B select @ID,'COM'+Ltrim(str(@COM))
fetch next from abc into @ID
set @COM=@COM+1
set @Count=@Count+1
if @COM>@SumOfPort
set @COM=1
end
break
end
CLOSE abc
DEALLOCATE abc
-------------------------------------------------
--查看结果,每个口子2条,共3个口
select * from B
-----------------------------
1 COM1
2 COM1
3 COM2
4 COM2
5 COM3
6 COM3
-----------------------------
--清空测试表
truncate table B当把set @SumOfPort=2 set @MaxOfPort=7 ,这就循环14次,可表里的数据第12条数据重复写了2次,补14条上了,如何让他不够14条最后多写那2条不让他写进去啊!急!
create table A
(
ID int primary key,
value int not null
)
--源表中数据
insert into A
select 1,11 union
select 2,12 union
select 3,13 union
select 4,14 union
select 5,15 union
select 6,16 union
select 7,17 union
select 8,18 union
select 9,19 union
select 10,20 union
select 11,21 union
select 12,22
GO
--目标表B
create table B
(
ID int primary key,
COM char(4)
)
GO
-------------------------------------------------
declare @COM int,@Count int,@ID int
declare
@SumOfPort int, --端口总数
@MaxOfPort int --每口最多几条
set @COM=1
set @Count=1
set @SumOfPort=3 --这里测试用3个端口
set @MaxOfPort=2 --这里测试用每口最多2条
declare abc cursor for
select ID from A
open abc
fetch next from abc into @ID
while @@FETCH_STATUS=0
begin
while @Count<=@SumOfPort*@MaxOfPort
begin
insert into B select @ID,'COM'+Ltrim(str(@COM))
fetch next from abc into @ID
set @COM=@COM+1
set @Count=@Count+1
if @COM>@SumOfPort
set @COM=1
end
break
end
CLOSE abc
DEALLOCATE abc
-------------------------------------------------
--查看结果,每个口子2条,共3个口
select * from B
-----------------------------
1 COM1
2 COM1
3 COM2
4 COM2
5 COM3
6 COM3
-----------------------------
--清空测试表
truncate table B当把set @SumOfPort=2 set @MaxOfPort=7 ,这就循环14次,可表里的数据第12条数据重复写了2次,补14条上了,如何让他不够14条最后多写那2条不让他写进去啊!急!
create table A
(
ID int primary key,
value int not null
)
--源表中数据
insert into A
select 1,11 union
select 2,12 union
select 3,13 union
select 4,14 union
select 5,15 union
select 6,16 union
select 7,17 union
select 8,18 union
select 9,19 union
select 10,20 union
select 11,21 union
select 12,22
GO
--目标表B
create table B
(
ID int ,
COM char(4)
)
GO
-------------------------------------------------
declare @COM int,@Count int,@ID int
declare
@SumOfPort int, --端口总数
@MaxOfPort int --每口最多几条
set @COM=1
set @Count=1
set @SumOfPort=2 --这里测试用3个端口
set @MaxOfPort=7 --这里测试用每口最多2条
declare abc cursor for
select ID from A
open abc
fetch next from abc into @ID
while @@FETCH_STATUS=0
begin
while (@Count <=@SumOfPort*@MaxOfPort) and (@Count <= (select count(1) from a))
begin
insert into B select @ID,'COM'+Ltrim(str(@COM))
fetch next from abc into @ID
set @COM=@COM+1
set @Count=@Count+1
if @COM>@SumOfPort
set @COM=1
end
break
end
CLOSE abc
DEALLOCATE abc
-------------------------------------------------
--查看结果,
select * from B
----------------------------- ID COM
----------- ----
1 COM1
2 COM2
3 COM1
4 COM2
5 COM1
6 COM2
7 COM1
8 COM2
9 COM1
10 COM2
11 COM1
12 COM2(所影响的行数为 12 行)
-----------------------------
--清空测试表
drop table B ,a
create table A
(
ID int primary key,
value int not null
)
--源表中数据
insert into A
select 1,11 union
select 2,12 union
select 3,13 union
select 4,14 union
select 5,15 union
select 6,16 union
select 7,17 union
select 8,18 union
select 9,19 union
select 10,20 union
select 11,21 union
select 12,22
GO ------------------------------------------------- declare @SumOfPort int --端口总数 set @SumOfPort=2 --这里测试用2个端口 select id,'com'+ltrim((id-1)%@SumOfPort+1) as com from a
/*
ID COM
----------- ----
1 COM1
2 COM2
3 COM1
4 COM2
5 COM1
6 COM2
7 COM1
8 COM2
9 COM1
10 COM2
11 COM1
12 COM2(所影响的行数为 12 行)
*/
-----------------------------
--清空测试表
drop table a
根据端口总数及分别生成连续的com+ID 吗
我把其中的5条数据改成flag=0,其它是空值 ,又出现原来的问题了,最后一条,第5条重复写入了
然后我把其中的5条数据FLAG改成0,其它空
这样我检索在加一个条件,flag=0
select ID from A where flag=0
这样以后,最后一条第5条又重复写入了!
不知什么原因
(
ID int primary key,
value int not null,
flag int
)
--源表中数据
insert into A
select 1,11,0 union
select 2,12,0 union
select 3,13,1 union
select 4,14,0 union
select 5,15,1 union
select 6,16,1 union
select 7,17,0 union
select 8,18,0 union
select 9,19,1 union
select 10,20,1 union
select 11,21,0 union
select 12,22,1
GO ------------------------------------------------- declare @SumOfPort int --端口总数 set @SumOfPort=2 --这里测试用2个端口 select id,'com'+ltrim((px-1)%@SumOfPort+1) as com
from
(select px=(select count(1) from a where flag = 1 and id <= t.id) ,* from a t where flag = 1) b/*
id com
----------- ---------------
3 com1
5 com2
6 com1
9 com2
10 com1
12 com2(所影响的行数为 6 行)
*/
-----------------------------
--清空测试表
drop table a
declare
@SumOfPort int, --端口总数
@MaxOfPort int --每口最多几条
set @COM=1
set @Count=1
set @SumOfPort=8 --这里测试用3个端口
set @MaxOfPort=4 --这里测试用每口最多2条
declare abc cursor for
select ID from AA where flag=0
open abc
fetch next from abc into @ID
while @@FETCH_STATUS=0
begin
while (@Count <=@SumOfPort*@MaxOfPort) and (@Count <= (select count(1) from aa where flag=0))
begin
insert into BB select @ID,'COM'+Ltrim(str(@COM))
fetch next from abc into @ID
set @COM=@COM+1
set @Count=@Count+1
if @COM>@SumOfPort
set @COM=1
end
break
end
CLOSE abc
DEALLOCATE abc
--delete from BBselect * from BB order by id
select * from AAinsert into AA
select 1,11 union
select 2,12 union
select 3,13 union
select 4,14 union
select 5,15 union
select 6,16 union
select 7,17 union
select 8,18 union
select 9,19 union
select 10,20 union
select 11,21 union
select 12,22
GO
select id,'com'+ltrim((px-1)%@SumOfPort+1) as com
from
(select px=(select count(1) from aa where flag = 0 and id <= t.id) ,* from aa t where flag = 0) b这个要放在哪呢?
begin
insert into BB select @ID,'COM'+Ltrim(str(@COM))
UPDATE AA set flag=1 where @id=@id
fetch next from abc into @ID
set @COM=@COM+1
set @Count=@Count+1
if @COM>@SumOfPort
set @COM=1
end
break
end
CLOSE abc
DEALLOCATE abc