花了比较长的时间写的,希望对楼主有帮助:create table 表1(A varchar(50),B varchar(50),C varchar(50),D int)
insert into 表1 select 'aa','ac00001','ac00100',100
insert into 表1 select 'aa','ac00501','ac00800',300
insert into 表1 select 'aa','ac00401','ac00450',50
insert into 表1 select 'cc','af00011','af00500',490 create table 表2(A varchar(50),B varchar(50),C varchar(50),D int)
insert into 表2 select 'aa','ac00001','ac01000',1000
insert into 表2 select 'cc','af00001','af06000',6000
insert into 表2 select 'aa','ac01001','ac02000',1000
insert into 表2 select 'cc','af06001','af10000',4000 --用存储过程直接改变表1的数据
create proc wsp
as
declare @t1 table(A varchar(50),B varchar(50),C varchar(50),px int)
declare @t2 table(A varchar(50),B varchar(50),C varchar(50),px int)
declare @t3 table(A varchar(50),B varchar(50),C varchar(50),D int)
insert into @t1 select a,cast(right(b,len(b)-2) as int) b,cast(right(c,len(c)-2) as int)c,px=(select count(1) from 表1 where a=a.a and b<a.b)+1 from 表1 a group by a,b,c order by a,b
insert into @t2 select a,cast(right(b,len(b)-2) as int) b,cast(right(c,len(c)-2) as int)c,px=(select count(1) from 表2 where a=a.a and b<a.b)+1 from 表2 a group by a,b,c order by a,b
insert into @t1(a,b,c)
select a,(case when px=1 and b!=1 then 1 else (select cast(c as int)+1 from @t1 where px=k.px-1 and a=k.a) end)b,
(case when px=1 and b!=1 then cast(b as int)-1 else (select cast(k.b as int)-1 from @t1 where px=k.px-1 and a=k.a) end)c
from @t1 k
delete @t1 where isnull(b,'')=''
insert into @t1(a,b,c) select k1.a,cast(k1.c as int)+1 as b,k2.c from
(select a,b,c from @t1 k where px=(select max(px) from @t1 where a=k.a group by a))k1,
(select a,b,c from @t2 k where px=(select min(px) from @t2 where a=k.a group by a))k2
where k1.a=k2.a
insert into @t1(a,b,c) select a,b,c from @t2 k where px=(select max(px) from @t2 where a=k.a group by a)
delete @t2
insert into @t2(a,b,c) select a,b,c from @t1 k where b not in (select cast(right(b,len(b)-2) as int) b from 表1 where a=k.a)
delete 表1
insert into 表1 (a,b,c) select a,b,c from @t2
update 表1 set b=left(表2.b,2)+ right('00000'+表1.b,5),c=left(表2.c,2)+right('00000'+表1.c,5),D=cast(表1.c as int)-cast(表1.b as int)+1 from 表2 where 表2.a=表1.a
select * from 表1调用:exec wspdrop table 表1
drop table 表2
考虑到直接改表1的数据可能对楼主使用有影响。所以改成使用表变量来存储结果。把存储过程稍微改下:
create proc wsp
as
declare @t1 table(A varchar(50),B varchar(50),C varchar(50),px int)
declare @t2 table(A varchar(50),B varchar(50),C varchar(50),px int)
declare @t3 table(A varchar(50),B varchar(50),C varchar(50),D int)
insert into @t1 select a,cast(right(b,len(b)-2) as int) b,cast(right(c,len(c)-2) as int)c,px=(select count(1) from 表1 where a=a.a and b<a.b)+1 from 表1 a group by a,b,c order by a,b
insert into @t2 select a,cast(right(b,len(b)-2) as int) b,cast(right(c,len(c)-2) as int)c,px=(select count(1) from 表2 where a=a.a and b<a.b)+1 from 表2 a group by a,b,c order by a,b
insert into @t1(a,b,c)
select a,(case when px=1 and b!=1 then 1 else (select cast(c as int)+1 from @t1 where px=k.px-1 and a=k.a) end)b,
(case when px=1 and b!=1 then cast(b as int)-1 else (select cast(k.b as int)-1 from @t1 where px=k.px-1 and a=k.a) end)c
from @t1 k
delete @t1 where isnull(b,'')=''
insert into @t1(a,b,c) select k1.a,cast(k1.c as int)+1 as b,k2.c from
(select a,b,c from @t1 k where px=(select max(px) from @t1 where a=k.a group by a))k1,
(select a,b,c from @t2 k where px=(select min(px) from @t2 where a=k.a group by a))k2
where k1.a=k2.a
insert into @t1(a,b,c) select a,b,c from @t2 k where px=(select max(px) from @t2 where a=k.a group by a)
delete @t2
insert into @t2(a,b,c) select a,b,c from @t1 k where b not in (select cast(right(b,len(b)-2) as int) b from 表1 where a=k.a)
update @t2 set b=left(表2.b,2)+ right('00000'+k.b,5),c=left(表2.c,2)+right('00000'+k.c,5),px=cast(k.c as int)-cast(k.b as int)+1 from 表2,@t2 k where 表2.a=k.a
select * from @t2还是一样的调用:exec wsp
删除,这个是我测试时用了的忘记删除了`
create function dbo.regexReplace
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
endreturn @result
end
GO
create table #表1(A varchar(10),B varchar(50),C varchar(50),D int )
insert #表1 select
'aa ', 'aa00001 ', 'aa00100 ',100 insert #表1 select
'aa ', 'aa00501 ', 'aa00800 ',300 insert #表1 select
'aa ', 'aa00401 ', 'aa00450 ',50 insert #表1 select
'cc ', 'aa00011 ', 'aa00500 ',490 insert #表1 select
'aa ', 'aa00801 ', 'aa00950 ',150 insert #表1 select
'cc ', 'aa06000 ', 'aa06001 ',1 insert #表1 select
'cc ', 'cc00001 ', 'cc00001 ',1 insert #表1 select
'aa ', 'aa00200 ', 'aa00300 ',101
create table #表2 (A varchar(10),B varchar(50),C varchar(50),D int )
insert #表2 select
'aa ', 'aa00001 ', 'aa01000 ',1000 insert #表2 select
'cc ', 'cc00001 ', 'cc06000 ',6000 insert #表2 select
'aa ', 'aa01001 ', 'aa02000 ',1000 insert #表2 select
'cc ', 'cc06001 ', 'cc07000 ',1000 insert #表2 select
'cc ', 'cc07001 ', 'cc08000 ',1000 insert #表2 select
'cc ', 'cc08001 ', 'cc10000 ',2000 insert #表2 select
'dd ', 'dd00001 ', 'dd02000 ',2000 --把B和C转换成数字
SELECT A,
B=CAST(dbo.regexReplace(B,'[^0-9]','',1,1) AS INT),
C=CAST(dbo.regexReplace(C,'[^0-9]','',1,1) AS INT),
D
INTO #1
FROM #表1 ORDER BY ASELECT A,
B=CAST(dbo.regexReplace(B,'[^0-9]','',1,1) AS INT),
C=CAST(dbo.regexReplace(C,'[^0-9]','',1,1) AS INT),
D
INTO #2
FROM #表2 ORDER BY A --排序,这里F01为0表示是开始数字,1为结束数字,F02为1表示为表1, 2表示为表2
SELECT ID = IDENTITY(INT,1,1),*
INTO #3
FROM
(
SELECT A,B,F01 = 0,F02 = 1 FROM #1
UNION ALL
SELECT A,C,F01 = 1,F02 = 1 FROM #1
UNION ALL
SELECT A,B,F01 = 0,F02 = 2 FROM #2
UNION ALL
SELECT A,C,F01 = 1,F02 = 2 FROM #2
) A ORDER BY A,B,F01, F02 SELECT * FROM #3
--去掉表1和表2中相同的数据
DELETE FROM #3 WHERE ID IN
(
SELECT A.ID FROM #3 A,#3 B WHERE A.F02 = 2 AND A.B = B.B AND B.F02 = 1 AND A.A = B.A
)
--因为去掉过相同的数据,所以必须填补一个
INSERT INTO #3
SELECT B.A,B.B+1,CASE WHEN A.F01 = 1 THEN 0 ELSE 1 END,A.F02 FROM #3 A INNER JOIN #3 B ON A.F01=B.F01 AND A.ID = B.ID+1 AND A.A = B.A --再次排序
SELECT id=identity(INT,1,1),A,B,F01 INTO # FROM #3 ORDER BY A,B,F01,F02
--结果语句
SELECT * FROM
(
SELECT A.A,B=RTRIM(LTRIM(A.A))+RIGHT(1000000+CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END,5),
C = RTRIM(LTRIM(A.A))+RIGHT(1000000+CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END,5),
--在求D的,我看楼主的逻辑是四舍五入,如果只有一位数,并且小于5的话,那么就不进行四舍五入
D = ROUND(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END,
CASE WHEN LEN(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END) = 1
THEN
CASE WHEN CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END<5
THEN 0
ELSE -1
END
ELSE
-LEN(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END)+1 END
)
FROM # A INNER JOIN # B ON A.A = B.A AND A.ID = B.ID - 1 AND (B.B - A.B>1 OR A.F01<B.F01)
) A WHERE NOT EXISTS ( SELECT * FROM #表1 B WHERE B.A = A.A AND A.B = B.B AND A.C = B.C )
--删除环境
DROP TABLE #1,#2,#3,#
DROP FUNCTION regexReplace
DROP TABLE #表1,#表2
--#表1中的数据
A B C D
---------- -------------------------------------------------- -------------------------------------------------- -----------
aa aa00001 aa00100 100
aa aa00200 aa00300 101
aa aa00401 aa00450 50
aa aa00501 aa00800 300
aa aa00801 aa00950 150
cc aa00011 aa00500 490
cc aa06000 aa06001 1
cc cc00001 cc00001 1(所影响的行数为 8 行)
--,#表2中的数据
A B C D
---------- -------------------------------------------------- -------------------------------------------------- -----------
aa aa00001 aa01000 1000
aa aa01001 aa02000 1000
cc cc00001 cc06000 6000
cc cc06001 cc07000 1000
cc cc07001 cc08000 1000
cc cc08001 cc10000 2000
dd dd00001 dd02000 2000(所影响的行数为 7 行)----结果数据
A B C D
---------- -------------------- -------------------- -----------
aa aa00101 aa00199 100
aa aa00301 aa00400 100
aa aa00451 aa00500 50
aa aa00951 aa01000 50
aa aa01001 aa02000 1000
cc cc00002 cc00010 10
cc cc00011 cc00500 500
cc cc00501 cc05999 5000
cc cc06000 cc06001 1
cc cc06002 cc07000 1000
cc cc07001 cc08000 1000
cc cc08001 cc10000 2000
dd dd00001 dd02000 2000(所影响的行数为 13 行)
--用来驱除字母的函数
create function dbo.regexReplace
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
endreturn @result
end
GO
create table #表1(A varchar(10),B varchar(50),C varchar(50),D int )
insert #表1 select
'aa ', 'aa00001 ', 'aa00100 ',100 insert #表1 select
'aa ', 'aa00501 ', 'aa00800 ',300 insert #表1 select
'aa ', 'aa00401 ', 'aa00450 ',50 insert #表1 select
'cc ', 'cc00011 ', 'cc00500 ',490 insert #表1 select
'aa ', 'aa00801 ', 'aa00950 ',150 insert #表1 select
'cc ', 'cc06000 ', 'cc06001 ',1 insert #表1 select
'cc ', 'cc00001 ', 'cc00001 ',1 insert #表1 select
'aa ', 'aa00200 ', 'aa00300 ',101
create table #表2 (A varchar(10),B varchar(50),C varchar(50),D int )
insert #表2 select
'aa ', 'aa00001 ', 'aa01000 ',1000 insert #表2 select
'cc ', 'cc00001 ', 'cc06000 ',6000 insert #表2 select
'aa ', 'aa01001 ', 'aa02000 ',1000 insert #表2 select
'cc ', 'cc06001 ', 'cc07000 ',1000 insert #表2 select
'cc ', 'cc07001 ', 'cc08000 ',1000 insert #表2 select
'cc ', 'cc08001 ', 'cc10000 ',2000 insert #表2 select
'dd ', 'dd00001 ', 'dd02000 ',2000 --把B和C转换成数字
SELECT A,
B=CAST(dbo.regexReplace(B,'[^0-9]','',1,1) AS INT),
C=CAST(dbo.regexReplace(C,'[^0-9]','',1,1) AS INT),
D
INTO #1
FROM #表1 ORDER BY ASELECT A,
B=CAST(dbo.regexReplace(B,'[^0-9]','',1,1) AS INT),
C=CAST(dbo.regexReplace(C,'[^0-9]','',1,1) AS INT),
D
INTO #2
FROM #表2 ORDER BY A --排序,这里F01为0表示是开始数字,1为结束数字,F02为1表示为表1, 2表示为表2
SELECT ID = IDENTITY(INT,1,1),*
INTO #3
FROM
(
SELECT A,B,F01 = 0,F02 = 1 FROM #1
UNION ALL
SELECT A,C,F01 = 1,F02 = 1 FROM #1
UNION ALL
SELECT A,B,F01 = 0,F02 = 2 FROM #2
UNION ALL
SELECT A,C,F01 = 1,F02 = 2 FROM #2
) A ORDER BY A,B,F01, F02 SELECT * FROM #3
--去掉表1和表2中相同的数据
DELETE FROM #3 WHERE ID IN
(
SELECT A.ID FROM #3 A,#3 B WHERE A.F02 = 2 AND A.B = B.B AND B.F02 = 1 AND A.A = B.A
)
--因为去掉过相同的数据,所以必须填补一个
INSERT INTO #3
SELECT B.A,B.B+1,CASE WHEN A.F01 = 1 THEN 0 ELSE 1 END,A.F02 FROM #3 A INNER JOIN #3 B ON A.F01=B.F01 AND A.ID = B.ID+1 AND A.A = B.A --再次排序
SELECT id=identity(INT,1,1),A,B,F01 INTO # FROM #3 ORDER BY A,B,F01,F02
--结果语句
SELECT * FROM
(
SELECT A.A,B=RTRIM(LTRIM(A.A))+RIGHT(1000000+CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END,5),
C = RTRIM(LTRIM(A.A))+RIGHT(1000000+CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END,5),
--在求D的,我看楼主的逻辑是四舍五入,如果只有一位数,并且小于5的话,那么就不进行四舍五入
D = ROUND(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END,
CASE WHEN LEN(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END) = 1
THEN
CASE WHEN CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END<5
THEN 0
ELSE -1
END
ELSE
-LEN(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END)+1 END
)
FROM # A INNER JOIN # B ON A.A = B.A AND A.ID = B.ID - 1 AND (B.B - A.B>1 OR A.F01<B.F01)
) A WHERE NOT EXISTS ( SELECT * FROM #表1 B WHERE B.A = A.A AND A.B = B.B AND A.C = B.C )
--删除环境
DROP TABLE #1,#2,#3,#
DROP FUNCTION regexReplace
DROP TABLE #表1,#表2A B C D
---------- -------------------- -------------------- -----------
aa aa00101 aa00199 100
aa aa00301 aa00400 100
aa aa00451 aa00500 50
aa aa00951 aa01000 50
aa aa01001 aa02000 1000
cc cc00002 cc00010 10
cc cc00501 cc05999 5000
cc cc06002 cc07000 1000
cc cc07001 cc08000 1000
cc cc08001 cc10000 2000
dd dd00001 dd02000 2000(所影响的行数为 11 行)
你给的代码好象不是我想要的,我往表1和表2中加了一个数据就不对了。怎么回事啊?
比如:
我在表1里加入:
A,B,C,D
cc,afa6051,af0700,950
得到的结果就不对了。这是怎么回事啊?
服务器: 消息 229,级别 14,状态 5,过程 regexReplace,行 15
拒绝了对对象 'sp_OACreate'(数据库 'master',所有者 'dbo')的 EXECUTE 权限。
语句已终止。
服务器: 消息 229,级别 14,状态 5,过程 regexReplace,行 15
拒绝了对对象 'sp_OACreate'(数据库 'master',所有者 'dbo')的 EXECUTE 权限。
语句已终止。
是我的权限不够么?
insert #表1 select
'aa ',1,100,100 insert #表1 select
'aa ',501,800,300 insert #表1 select
'aa ',401,450,50 insert #表1 select
'cc ',11,500,490 insert #表1 select
'aa ',801,950,150 insert #表1 select
'cc ',6000,6000,1 insert #表1 select
'cc ',1,1,1
create table #表2 (A varchar(10),B int,C int,D int )
insert #表2 select
'aa ',1,1000,1000 insert #表2 select
'cc ',1,6000,6000 insert #表2 select
'aa ',1001,2000,1000 insert #表2 select
'cc ',6001,7000,1000 insert #表2 select
'cc ',7002,8000,999 declare @ta table(A varchar(10),B int,C int,id int identity(1,1))
insert @ta select A,B,C+1 C from #表1 union select A,C+1,C+1 from #表2 union select A,B,B from #表2
order by A,B,C
---select * from @ta
select A,BB B,CC C,CC-BB+1 D from
(
select A,C BB,id,CC=(select top 1 B-1 from @ta tb where ta.A=tb.A and ta.id <tb.id order by tb.id)
from @ta ta
)a where CC is not null and CC-BB+1 < >0 order by id
这个是没有加字母的,是纯数字的,但是要加数据我就不知道怎么加了?能不能指点指点啊》?
cc,afa6051,af0700,950
你说过B:开始的数字
C:结束的数字可你这里插入的数据不合理噢。
cc,af6051,af0700,950
因为表2中有这个数据的范围啊,所以表1中可以加这些数据的啊?这是没有问题的啊,是不是啊?
还有就是我在表1中加入:
A,B,C,D
dd,af11001,af12000,1000
在表2中加入
A,B,C,D
dd,af10001,af15000,5000
这样得到的结果也是不对的。
我在说一下表1和表2的功能吧
表1中出现的B,C字段的范围在表2中必须存在的。
比如:
表1中有一条数据:
aa,aa02001,aa03000,1000
表2中有一条数据:
aa,aa020001,aa05000,3000
我要的结果就是
aa,aa03001,aa05000,2000
要是用你的代码写得到的结果是
aa,aa00001,aa02000,2000
aa,aa020001,aa05000,3000
这样就多了一条记录了
可能aa00001,aa02000这个范围是“cc"的也说不好的。
这样您能明白么?
create function dbo.regexReplace
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小写
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
endreturn @result
end
GO
create table #表1(A varchar(10),B varchar(50),C varchar(50),D int )
insert #表1 select
'aa ', 'aa00001 ', 'aa00100 ',100 insert #表1 select
'aa ', 'aa00501 ', 'aa00800 ',300 insert #表1 select
'aa ', 'aa00401 ', 'aa00450 ',50 insert #表1 select
'cc ', 'cc00011 ', 'cc00500 ',490 insert #表1 select
'aa ', 'aa00801 ', 'aa00950 ',150 insert #表1 select
'cc ', 'cc06000 ', 'cc06001 ',1 insert #表1 select
'cc ', 'cc00001 ', 'cc00001 ',1 insert #表1 select
'aa ', 'aa00200 ', 'aa00300 ',101
create table #表2 (A varchar(10),B varchar(50),C varchar(50),D int )
insert #表2 select
'aa ', 'aa00001 ', 'aa01000 ',1000 insert #表2 select
'cc ', 'cc00001 ', 'cc06000 ',6000 insert #表2 select
'aa ', 'aa01001 ', 'aa02000 ',1000 insert #表2 select
'cc ', 'cc06001 ', 'cc07000 ',1000 insert #表2 select
'cc ', 'cc07001 ', 'cc08000 ',1000 insert #表2 select
'cc ', 'cc08001 ', 'cc10000 ',2000 insert #表2 select
'dd ', 'dd00001 ', 'dd02000 ',2000 --INSERT INTO #表1 VALUES ('cc','cc00051','cc00700',50)
--INSERT INTO #表2 VALUES ('cc','cc00051','cc00700',50)--把B和C转换成数字
SELECT A,
B=CAST(dbo.regexReplace(B,'[^0-9]','',1,1) AS INT),
C=CAST(dbo.regexReplace(C,'[^0-9]','',1,1) AS INT),
D
INTO #1
FROM #表1 ORDER BY ASELECT A,
B=CAST(dbo.regexReplace(B,'[^0-9]','',1,1) AS INT),
C=CAST(dbo.regexReplace(C,'[^0-9]','',1,1) AS INT),
D
INTO #2
FROM #表2 ORDER BY A --排序,这里F01为0表示是开始数字,1为结束数字,F02为1表示为表1, 2表示为表2
SELECT ID = IDENTITY(INT,1,1),*
INTO #3
FROM
(
SELECT A,B,F01 = 0,F02 = 1 FROM #1
UNION ALL
SELECT A,C,F01 = 1,F02 = 1 FROM #1
UNION ALL
SELECT A,B,F01 = 0,F02 = 2 FROM #2
UNION ALL
SELECT A,C,F01 = 1,F02 = 2 FROM #2
) A ORDER BY A,B,F01, F02SELECT * FROM #3 A INNER JOIN #3 B ON A.A = B.A AND A.ID = B.ID - 1 AND A.F01 = 0 AND A.F02 = 2 AND B.F01 = 1 AND B.F02 = 1
SELECT * FROM #3
--去掉表1和表2中相同的数据
DELETE FROM #3 WHERE ID IN
(
SELECT A.ID FROM #3 A,#3 B WHERE A.F02 = 2 AND A.B = B.B AND B.F02 = 1 AND A.A = B.A
UNION
SELECT A.ID FROM #3 A INNER JOIN #3 B ON A.A = B.A AND A.ID = B.ID - 1 AND A.F01 = 0 AND A.F02 = 2 AND B.F01 = 1 AND B.F02 = 1
)
--因为去掉过相同的数据,所以必须填补一个
INSERT INTO #3
SELECT B.A,CASE WHEN A.F01=0 THEN A.B-1 ELSE B.B+1 END, CASE WHEN A.F01=1 THEN 0 ELSE 1 END,A.F02
FROM #3 A INNER JOIN #3 B ON A.F01=B.F01 AND A.ID = B.ID+1 AND A.A = B.A --再次排序
SELECT id=identity(INT,1,1),A,B,F01 INTO # FROM #3 ORDER BY A,B,F01,F02 SELECT * FROM #表1
SELECT * FROM #表2
SELECT * FROM #
--结果语句
SELECT * FROM
(
SELECT A.A,B=RTRIM(LTRIM(A.A))+RIGHT(1000000+CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END,5),
C = RTRIM(LTRIM(A.A))+RIGHT(1000000+CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END,5),
--在求D的,我看楼主的逻辑是四舍五入,如果只有一位数,并且小于5的话,那么就不进行四舍五入
D = ROUND(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END,
CASE WHEN LEN(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END) = 1
THEN
CASE WHEN CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END<5
THEN 0
ELSE -1
END
ELSE
-LEN(CASE WHEN A.F01>B.F01 THEN B.B-1 ELSE B.B END - CASE WHEN A.F01>B.F01 THEN A.B+1 ELSE A.B END)+1 END
)
FROM # A INNER JOIN # B ON A.A = B.A AND A.ID = B.ID - 1 AND (B.B - A.B>1 OR A.F01<B.F01)
) A WHERE NOT EXISTS ( SELECT * FROM #表1 B WHERE B.A = A.A AND A.B = B.B AND A.C = B.C )
--删除环境
DROP TABLE #1,#2,#3,#
DROP FUNCTION regexReplace
DROP TABLE #表1,#表2用
INSERT INTO #表1 VALUES ('cc','cc00051','cc00700',50)
执行,数据倒是对的
A B C D
---------- -------------------- -------------------- -----------
aa aa00101 aa00199 100
aa aa00301 aa00400 100
aa aa00451 aa00500 50
aa aa00951 aa01000 50
aa aa01001 aa02000 1000
cc cc00002 cc00010 10
cc cc00011 cc00050 40
cc cc00051 cc00500 400
cc cc00501 cc00700 200
cc cc00701 cc05999 5000
cc cc06002 cc07000 1000
cc cc07001 cc08000 1000
cc cc08001 cc10000 2000
dd dd00001 dd02000 2000(所影响的行数为 14 行)
然后又用
INSERT INTO #表2 VALUES ('cc','cc00051','cc00700',50)
执行
A B C D
---------- -------------------- -------------------- -----------
aa aa00101 aa00199 100
aa aa00301 aa00400 100
aa aa00451 aa00500 50
aa aa00951 aa01000 50
aa aa01001 aa02000 1000
cc cc00002 cc00010 10
cc cc00501 cc00700 200
cc cc00701 cc05999 5000
cc cc06002 cc07000 1000
cc cc07001 cc08000 1000
cc cc08001 cc10000 2000
dd dd00001 dd02000 2000(所影响的行数为 12 行)--:( 忙就帮到这里了,楼主可以根据自己的数据再修改或者重新写,数据变化太大,所以结果不稳定.
DELETE FROM #3 WHERE ID IN
(
SELECT A.ID FROM #3 A,#3 B WHERE A.F02 = 2 AND A.B = B.B AND B.F02 = 1 AND A.A = B.A
UNION
SELECT A.ID FROM #3 A INNER JOIN #3 B ON A.A = B.A AND A.ID = B.ID - 1 AND A.F01 = 0 AND A.F02 = 2 AND B.F01 = 1 AND B.F02 = 1
)这里增加了
SELECT A.ID FROM #3 A INNER JOIN #3 B ON A.A = B.A AND A.ID = B.ID - 1 AND A.F01 = 0 AND A.F02 = 2 AND B.F01 = 1 AND B.F02 = 1第二个是
INSERT INTO #3
SELECT B.A,CASE WHEN A.F01=0 THEN A.B-1 ELSE B.B+1 END, CASE WHEN A.F01=1 THEN 0 ELSE 1 END,A.F02
FROM #3 A INNER JOIN #3 B ON A.F01=B.F01 AND A.ID = B.ID+1 AND A.A = B.A这里修改了CASE WHEN A.F01=0 THEN A.B-1 ELSE B.B+1 END
/*
这样的数据,应该是16进制。10进制将数据拆分再处理,原理一样。
*/--原始数据:@1
declare @1 table(A varchar(2),B varbinary(8),C varbinary(8))
insert @1
select 'aa',0xac00001,0xac00100 union all
select 'aa',0xac00501,0xac00800 union all
select 'aa',0xac00401,0xac00450 union all
select 'cc',0xaf00011,0xaf00500
--原始数据:@2
declare @2 table(A varchar(2),B varbinary(8),C varbinary(8))
insert @2
select 'aa',0xac00001,0xac01000 union all
select 'cc',0xaf00001,0xaf06000 union all
select 'aa',0xac01001,0xac02000 union all
select 'cc',0xaf06001,0xaf10000select id=identity(int,1,1),* into #T from @1 order by b,cselect
A=c.A,
B=isnull(cast(a.C+1 as varbinary),c.B),
C=isnull
(
cast(b.B-1 as varbinary),
(select min(C) from @2 where A=a.A)
)
into #R
from #T a
full join #T b on a.id=b.id-1 and a.A=b.A
full join (select A,B=min(B) from @2 group by A) c on isnull(a.A,b.A)=c.A
where isnull(b.B,0)<>c.Bdelete #R where B>=C
insert #R select * from @2 a where not exists (select 1 from #R where B=a.B or C=a.C)select * from #R order by A,B/*
A B C
-- ---------- ----------
aa 0x0AC00101 0x0AC00400
aa 0x0AC00451 0x0AC00500
aa 0x0AC00801 0x0AC01000
aa 0x0AC01001 0x0AC02000
cc 0x0AF00001 0x0AF00010
cc 0x0AF00501 0x0AF06000
cc 0x0AF06001 0x0AF10000
*/--删除测试
drop table #T,#R