-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(minID varchar(30),maxID varchar(30))
go
insert into tb
select
'A0300000000020' ,'A0300000000039' union all select
'A0300000000001' ,'A0300000000100'
go
select IDENTITY(int,1,1) as id,* into #
from(select minid from tb union all select maxid from tb ) t order by minid
select * from #
select minid=case when ID=1 then minid
else (select 'A'+cast(convert(bigint,right(minid,len(minid)-1))+1 as varchar(30)) from # where ID=t.id) end,
maxid=(select minid from # where ID=t.id+1)
from # t
where minid<(select MAX(minid) from #)
/*------------
minid maxid
------------------------------- ------------------------------
A0300000000001 A0300000000020
A300000000021 A0300000000039
A300000000040 A0300000000100(3 行受影响)
-------*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(minID varchar(30),maxID varchar(30))
go
insert into tb
select
'A0300000000020' ,'A0300000000039' union all select
'A0300000000001' ,'A0300000000100'
go
select IDENTITY(int,1,1) as id,* into #
from(select minid from tb union all select maxid from tb ) t order by minid
select * from #
select minid=case when ID=1 then minid
else (select 'A'+cast(convert(bigint,right(minid,len(minid)-1))+1 as varchar(30)) from # where ID=t.id) end,
maxid=(select minid from # where ID=t.id+1)
from # t
where minid<(select MAX(minid) from #)
/*------------
minid maxid
------------------------------- ------------------------------
A0300000000001 A0300000000020
A300000000021 A0300000000039
A300000000040 A0300000000100(3 行受影响)
-------*/
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(minID varchar(30),maxID varchar(30))
go
insert into tb
select
'A0300000000020' ,'A0300000000039' union all select
'A0300000000001' ,'A0300000000100'
go
select IDENTITY(int,1,1) as id,* into #
from(select minid from tb union all select maxid from tb ) t order by minid
select * from #
select minid=case when ID=1 then minid
else (select 'A0'+cast(convert(bigint,right(minid,len(minid)-1))+1 as varchar(30)) from # where ID=t.id) end,
maxid=(select minid from # where ID=t.id+1)
from # t
where minid<(select MAX(minid) from #)
/*------------
minid maxid
------------------------------- ------------------------------
minid maxid
-------------------------------- ------------------------------
A0300000000001 A0300000000020
A0300000000021 A0300000000039
A0300000000040 A0300000000100(3 行受影响)
(3 行受影响)
-------*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([grp] int,[id] varchar(14))
insert [tb]
select 1,'A0300000000001' union all
select 1,'A0300000000100' union all
select 2,'A0300000000020' union all
select 2,'A0300000000039'
go
--select * from [tb]select minID=min(id),maxID=max(id)
from
(
select id,rn=row_number() over(order by id)
from
(
select id=left(id,3)+right(replicate('0',10)+rtrim(right(id,11)+1),11)
from (select id,rn=row_number() over(order by id) from tb) t
where rn>1 and id<(select max(id) from tb)
union all
select id from tb
) tt
) ttt
group by (rn-1)/2
/*
minID maxID
---------------------------- ----------------------------
A0300000000001 A0300000000020
A0300000000021 A0300000000039
A0300000000040 A0300000000100(3 行受影响)
*/
declare @t table (
minid varchar(20),
maxid varchar(20)
)
insert into @t
select 'A0300000000020','A0300000000039' union all
select 'A0300000000001','A0300000000100' union all
select 'A0300000000021','A0300000000034'
select * from @tdeclare @temp table(
id1 varchar(20)
)
insert into @temp (id1)
select distinct minid from @t union all
select distinct maxid from @t
declare @temp2 table(
id int,
id1 varchar(20)
)
insert into @temp2(id,id1)
select row_number() over(order by cast(right(temp.id1,len(temp.id1)-3) as bigint)) ,id1 from @temp temp
order by cast(right(temp.id1,len(temp.id1)-3) as bigint)
select max(t1.id1) 'maxid',min(t3.id1) 'minid' from @temp2 t1
inner join @temp2 t3 on t1.id<t3.id
where t1.id%2=1
group by t1.id1
/*
minid maxid
A0300000000001 A0300000000020
A0300000000021 A0300000000034
A0300000000039 A0300000000100思路有点混乱,写的不大好,期待高手神作
*/
insert @tb select 'A0300000000001', 'A0300000000100'
insert @tb select 'A0300000000020', 'A0300000000040'
declare @ta table(minID varchar(20),maxID varchar(20))
insert @ta(minid)
select minid from @tb
union
select left(maxid,12)+ltrim(right(maxid,2)+1) from @tb where maxid<>(select max(maxid) from @tb)update a set maxid=(select top 1 minid from @ta where minid>a.minid order by minid) from @ta aupdate @ta set maxid=isnull(left(maxid,12)+ltrim(right(maxid,2)-1),(select max(maxid) from @tb))
select * from @ta/*
minID maxID
-------------------- --------------------
A0300000000001 A0300000000019
A0300000000020 A0300000000040
A0300000000041 A0300000000100(所影响的行数为 3 行)
*/
INSERT @TB
SELECT 'A0300000000020', 'A0300000000039' UNION ALL
SELECT 'A0300000000001', 'A0300000000100'SELECT ID,SEQ=IDENTITY(int,1,1)
INTO #
FROM (
SELECT [minID] AS ID FROM @TB
UNION ALL
SELECT [maxID] FROM @TB
) T
ORDER BY IDSELECT CASE WHEN A.SEQ>1 THEN LEFT(A.ID,3)+RIGHT(REPLICATE('0', 11)+RTRIM(CAST(RIGHT(A.ID,11) AS INT)+1), 11) ELSE A.ID END AS [minID],
B.ID AS [maxID]
FROM # AS A JOIN # AS B
ON B.SEQ=A.SEQ+1DROP TABLE #
/*
minID maxID
-------------- --------------
A0300000000001 A0300000000020
A0300000000021 A0300000000039
A0300000000040 A0300000000100
*/
declare @t table (
minid varchar(20),
maxid varchar(20)
)
insert into @t
select 'A0300000000020','A0300000000039' union all
select 'A0300000000001','A0300000000100' union all
select 'A0300000000002','A0300000000088' union all
select 'A0300000000003','A0300000000039' union all
select 'A0300000000006','A0300000000012' union all
select 'A0300000000008','A0300000000018' union all
select 'A0300000000012','A0300000000039' union all
select 'A0300000000018','A0300000000064' union all
select 'A0300000000060','A0300000000086' union all
select 'A0300000000085','A0300000000072' union all
select 'A0300000000093','A0300000000094' union all
select 'A0300000000072','A0300000000099' union all
select 'A0300000000021','A0300000000034'
select * from @tdeclare @temp table(
id1 varchar(20)
)
insert into @temp (id1)
select distinct minid from @t t1
union
select distinct maxid from @t t1declare @temp2 table(
id int,
id1 varchar(20)
)
insert into @temp2(id,id1)
select row_number() over(order by cast(right(temp.id1,len(temp.id1)-3) as bigint)) ,id1 from @temp temp
order by cast(right(temp.id1,len(temp.id1)-3) as bigint)select max(t1.id1) 'maxid'
,left(min(t3.id1),len(min(t3.id1))-len(cast(cast(right(min(t3.id1),len(min(t3.id1))-3) as bigint) as varchar(50))))+cast(cast(right(min(t3.id1),len(min(t3.id1))-3) as bigint) as varchar(50))
'minid' from @temp2 t1
inner join @temp2 t3 on t1.id<t3.id
group by t1.id1/*
minid maxid
A0300000000001 A0300000000002
A0300000000002 A0300000000003
A0300000000003 A0300000000006
A0300000000006 A0300000000008
A0300000000008 A0300000000012
A0300000000012 A0300000000018
A0300000000018 A0300000000020
A0300000000020 A0300000000021
A0300000000021 A0300000000034
A0300000000034 A0300000000039
A0300000000039 A0300000000060
A0300000000060 A0300000000064
A0300000000064 A0300000000072
A0300000000072 A0300000000085
A0300000000085 A0300000000086
A0300000000086 A0300000000088
A0300000000088 A0300000000093
A0300000000093 A0300000000094
A0300000000094 A0300000000099
A0300000000099 A0300000000100思路有点混乱,写的不大好,期待高手神作
这数据够乱的了吧
*/
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(minID varchar(30),maxID varchar(30))
go
insert into tb
select
'A0300000000020' ,'A0300000000039' union all select
'A0300000000001' ,'A0300000000100' union all select
'b0300000000001' ,'b0300000000100'union all select
'b0300000000020' ,'b0300000000039'
go
select IDENTITY(int,1,1) as id,* into #
from(select minid from tb union all select maxid from tb ) t order by minid
select * from #
select minid=case when ID=(select MIN(id)from # where LEFT(t.minid,4)=LEFT(minid,4)) then minid
else (select 'A'+cast(convert(bigint,right(minid,len(minid)-1))+1 as varchar(30)) from # where ID=t.id and LEFT(t.minid,4)=LEFT(minid,4)) end,
maxid=(select minid from # where ID=t.id+1)
from # t
where minid<(select MAX(minid) from #)minid maxid
------------------------------- ------------------------------
A0300000000001 A0300000000020
A300000000021 A0300000000039
A300000000040 A0300000000100
A300000000101 b0300000000001
b0300000000001 b0300000000020
A300000000021 b0300000000039
A300000000040 b0300000000100(7 行受影响)
A0300000000001 A0300000000020
A300000000021 A0300000000039
A300000000040 A0300000000100B300000000001 B0300000000020
B300000000021 B0300000000039
B300000000040 B0300000000100
我要这样的结果。。
from(select minid from tb union all select maxid from tb ) t order by minid
select * from #
select minid=case when ID=(select MIN(id)from # where LEFT(t.minid,4)=LEFT(minid,4)) then minid
else (select left(minid,1)+cast(convert(bigint,right(minid,len(minid)-1))+1 as varchar(30))
from # where ID=t.id and LEFT(t.minid,4)=LEFT(minid,4)) end,
maxid=(select minid from # where ID=t.id+1)
from # t
where minid<(select MAX(minid) from # )
这样
from(select minid from tb union all select maxid from tb ) t order by minid
select * from #
select minid=case when ID=(select MIN(id)from # where LEFT(t.minid,4)=LEFT(minid,4)) then minid
else (select left(minid,2)+cast(convert(bigint,right(minid,len(minid)-1))+1 as varchar(30))
from # where ID=t.id and LEFT(t.minid,4)=LEFT(minid,4)) end,
maxid=(select minid from # where ID=t.id+1)
from # t
where minid<(select MAX(minid) from # )
是这样 这样
insert into @t select 'A0300000000020','A0300000000039'
insert into @t select 'A0300000000001','A0300000000100'
insert into @t select 'B0300000000024','B0300000000059'
insert into @t select 'B0300000000074','B0300000000089'
insert into @t select 'B0300000000001','B0300000000100' select
a.minID,min(b.maxID) as maxID
from
(select minID from @t
union
select left(maxID,1)+right(100000000000000+1+stuff(maxID,1,1,''),13)
from @t t where exists(select 1 from @t where minID<t.minID and maxID>t.minID)) a,
(select maxID from @t
union
select left(minID,1)+right(100000000000000-1+stuff(minID,1,1,''),13)
from @t t where exists(select 1 from @t where minID<t.minID and maxID>t.minID)) b
where
a.minID<b.maxID
group by
a.minID/*
minID maxID
---------------------------- ----------------------------
A0300000000001 A0300000000019
A0300000000020 A0300000000039
A0300000000040 A0300000000100
B0300000000001 B0300000000023
B0300000000024 B0300000000059
B0300000000060 B0300000000073
B0300000000074 B0300000000089
B0300000000090 B0300000000100
*/