请问各位大哥,如何将table1和table2转换为table3
这张table1表示所有的编号
StartNumber EndNumber
000001 000010
000011 000020
000021 000030
000031 000040
000041 000050
这张table2表示使用过的编号
StartNumber EndNumber
000001 000001
000004 000007
000015 000017
000018 000018
000025 000026
000031 000040
000041 000041
000044 000046
这张table3表示所有编号的使用情况(1表示用过,0表示没用过)
startnumber endnumber type
000001 000001 1
000002 000003 0
000004 000007 1
000008 000010 0
000011 000014 0
000015 000017 1
000018 000018 1
000019 000020 0
000021 000024 0
000025 000026 1
000027 000030 0
000031 000040 1
000041 000041 1
000042 000043 0
000044 000046 1
000047 000050 0
这张table1表示所有的编号
StartNumber EndNumber
000001 000010
000011 000020
000021 000030
000031 000040
000041 000050
这张table2表示使用过的编号
StartNumber EndNumber
000001 000001
000004 000007
000015 000017
000018 000018
000025 000026
000031 000040
000041 000041
000044 000046
这张table3表示所有编号的使用情况(1表示用过,0表示没用过)
startnumber endnumber type
000001 000001 1
000002 000003 0
000004 000007 1
000008 000010 0
000011 000014 0
000015 000017 1
000018 000018 1
000019 000020 0
000021 000024 0
000025 000026 1
000027 000030 0
000031 000040 1
000041 000041 1
000042 000043 0
000044 000046 1
000047 000050 0
from t2
left join t1 on t1.startnumber and t2.startnumber
应该好看懂得,你再仔细看看,谢谢!
比如说编号1-1000没用过,
101-200用过了
那么就是剩下1-100,201-1000没用过,table3表示起来就是,
1 100 0
101 200 1
201 1000 0
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[fill]
@s int,
@e int
as
begin
--declare table #tb (sNum int ,eNum int,[type] int)
declare @x int
declare @y int
select top 1 @x=sNum,@y=eNum from t2 where t2.sNum>=@s and t2.eNum<=@e order by sNum asc
while(@x>0)
begin --
if(@x<=@s)
begin
insert t3 select @s,@y,1
set @s=@y+1
end
else
begin
insert t3 select @s,@x-1,0
set @s=@x
end
select @x=count(1) from t2 where t2.sNum>=@s and t2.eNum<=@e
if(@x>0)
select top 1 @x=sNum,@y=eNum from t2 where t2.sNum>=@s and t2.eNum<=@e order by sNum asc
else
if(@s<=@e)
insert t3 select @s,@e,0
end --
enduse db
go
declare cr_readt1 cursor for
select sNum,eNum from t1 order by sNumdeclare @s int
declare @e int
open cr_readt1while @@fetch_status =0
begin
fetch next from cr_readt1
into @s,@e EXEC [dbo].[fill]
@s = @s,
@e = @e
end
close cr_readt1sNum eNum type
----------- ----------- -----------
1 1 1
2 3 0
4 7 1
8 10 0
11 14 0
15 17 1
18 18 1
19 20 0
21 24 0
25 26 1
27 30 0
31 40 1
41 41 1
42 43 0
44 46 1
47 50 0
41 41 1
42 43 0
44 46 1
47 50 0(20 行受影响)
是用的整数测试,先要用一个函数把字符串转化成整数
000018 000018那为什么不是一行 000015 000018
应该是这样的。use db
go
drop table t1
create table t1(sNum int,eNum int)
insert t1
select 1,10 union all
select 11,20 union all
select 21,30 union all
select 31,40 union all
select 41,50 drop table t2
create table t2 (sNum int,eNum int)
insert t2
select 1,1 union all
select 4,7 union all
select 15,17 union all
select 18,18 union all
select 25,26 union all
select 31,40 union all
select 41,41 union all
select 44,46drop table t3
create table t3(sNum int ,eNum int,[type] int)
use db
go
declare cr_readt1 cursor for
select sNum,eNum from t1 order by sNumdeclare @s int
declare @e int
open cr_readt1
fetch next from cr_readt1
into @s,@ewhile @@fetch_status=0
begin
select @s,@e
EXEC [dbo].[fill]
@s = @s,
@e = @e
fetch next from cr_readt1
into @s,@e
end
close cr_readt1
DEALLOCATE cr_readt1sNum eNum type
----------- ----------- -----------
1 1 1
2 3 0
4 7 1
8 10 0
11 14 0
15 17 1
18 18 1
19 20 0
21 24 0
25 26 1
27 30 0
31 40 1
41 41 1
42 43 0
44 46 1
47 50 0(16 行受影响)