????关于自然数的顺序连接问题,求SQL语句或解决方法!(在线)

Question

表A
----------------------------------------------------
StartTickNo          EndTickNo           TickCount
000001001            000002000           1000
000002001            000003000           1000
000007001            000008000           1000
000008001            000009000           1000
000009001            000010000           1000 先按表A的StartTickNo排序,若上一行的EndTickNO+1与下一行的StartTickNo相等,则把上一行的开始号和下一行的结束号相组成一行,依次往下推,若组合后的结束号和下一行的开始号是连号则继续组合.
并把TickCount相加,数字长度为9位 结果:
-----------------------------------------------
StartTickNo         EndTickNo           TickCount
000001001           000003000             2000
000007001           000010000             3000 
-------------------------------------------------
在线等待!
Thanks

Answer 1

--try看看,數據夠規律的話,可以取个巧,理解成單叉樹,取最低層的葉create table T(startNO varchar(10),endNO varchar(10), nun int)
insert into T
select '000001001','000002000',1000  union  all
select '000002001','000003000',2000  union  all
select '000007001','000008000',3000  union  all
select '000008001','000009000',4000  union  all
select '000009001','000010000',5000  union allselect '000011000','000011005' ,6000 union all 
select '000011006','000011010' ,7000 GO
create function fn_test(@startNO varchar(10))
returns  varchar(10)
AS
begin
declare @t  table (endNO varchar(10),level int)
declare @level int
  set @level=0
  insert into @t select  endNO,@level from T where startNO=@startNO
while @@rowcount>0
begin
   set @level=@level+1
   insert into @t  select a.endNO,@level
  from T a,@t b
  where convert(int ,a.startNO)=convert(int,b.endNO)+1
  and b.level=@level-1
end
declare @str varchar(10)
select @str=max(endNO) from @t
return @str
endGO
select min(startNO) as startNO,endNO,sum(nun) as num from 
(select startNO,dbo.fn_test(startNO) as endNO ,nun  from T) A
group by endNO/*
startNO    endNO      num         
---------- ---------- ----------- 
000001001  000003000  3000
000007001  000010000  12000
000011000  000011010  13000
*/drop table T
drop function fn_test

Answer 2

楼上的你是怎么想出来的呢?
我看都还没看懂.
佩服ing!

Answer 3

--建立测试数据create table Test
(
StartTickNo varchar(9),
EndTickNo   varchar(9),
TickCount   int
)
goinsert into Test
select '000001001','000002000',1000
union all select
'000002001','000003000',1000
union all select
'000007001','000008000',1000
union all select
'000008001','000009000',1000
union all select
'000009001','000010000',1000 
go--通过存储过程实现：create proc up_test
as
begin
declare @a  table(StartTickNo varchar(9),EndTickNo varchar(9),TickCount int)    insert into @a select top 1 * from Test order by StartTickNo declare @LastBegin int
declare @NowBegin int

declare @LastEnd int
declare @NowEnd int declare @LastCount int
declare @NowCount int select @LastBegin = cast(StartTickNo as int),
   @LastEnd = cast(EndTickNo as int),
   @LastCount = TickCount from @a    declare cursors cursor for
select cast(StartTickNo as int),cast(EndTickNo as int),TickCount
 from Test 
where cast(StartTickNo as int) != @LastBegin
order by StartTickNo
 
open cursors
fetch next from cursors into @NowBegin,@NowEnd,@NowCount
while @@Fetch_Status = 0
begin 
if @LastEnd + 1 = @NowBegin
begin
set @LastCount = @NowCount + @LastCount
set @LastEnd  =  @NowEnd
update @a 
set EndTickNo = right('000000000' + cast(@NowEnd as varchar(9)),9),
TickCount = @LastCount
where cast(StartTickNo as int) = @LastBegin
end
else
begin
insert into @a
select * from Test where cast(StartTickNo as int) = @NowBegin set @LastBegin = @NowBegin
set @LastCount = @NowCount
set @LastEnd  =  @NowEnd 
end
fetch next from cursors into @NowBegin,@NowEnd,@NowCount
end
close cursors
deallocate cursors select * from @a
end--测试结果：
运行：
exec up_test
结果：
StartTickNo         EndTickNo           TickCount
000001001           000003000             2000
000007001           000010000             3000 --说明：存储过程用的是2005的表变量，如果是2000的话把表变量改成临时表就可以了

Answer 4

playwarcraft(时间就像乳沟,挤挤还是有的) ( ) 
---------------------
试了一下,是可以.
但是很慢.
335行记录,用了33秒.

Answer 5

GoldShield(李柏岑) ( ) 信誉：100    Blog   加为好友  2007-04-25 15:37:34  得分: 0  
 
 试了一下,是可以.
但是很慢.
335行记录,用了33秒.
--------------------------------------------
不是吧,你先把function建好,
直接跑下面的查詢語句,應該不用這麼長的吧.  
 

Answer 6

--建表
CREATE TABLE [dbo].[Tick_IN](
[in_SN] [int]   NULL,
[MSN] [int] NULL,
[TS_SN] [varchar](3)  NULL,
[PJBM] [char](4)  NULL,
[in_StartNo] [char](18)  NULL,
[in_EndNo] [char](18)  NULL,
[in_Count] [int] NULL,
[in_Emp] [varchar](10)  NULL,
[in_EmployeeNo] [varchar](10)  NULL,
[in_Time] [datetime] NULL,
[LastCount] [int] NULL,
[Flag] [char](1) 
) ON [PRIMARY]

Answer 7

--部分数据
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417200016        ' ,    '0417210006        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417210017        ' ,    '0417220007        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417220018        ' ,    '0417230008        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417230010        ' ,    '0417240000        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417240011        ' ,    '0417250001        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417250012        ' ,    '0417260002        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417260013        ' ,    '0417270003        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417270014        ' ,    '0417280004        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417280015        ' ,    '0417290005        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417290016        ' ,    '0417300006        ' ,    1000        ,    '0001  '     ,    '谌永平    '    ,    '2006-10-21 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417300017        ' ,    '0417310007        ' ,    1000        ,    '0002  '     ,    '蒲小琴    '    ,    '2006-10-26 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417310018        ' ,    '0417320008        ' ,    1000        ,    '0002  '     ,    '蒲小琴    '    ,    '2006-10-26 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417320010        ' ,    '0417330000        ' ,    1000        ,    '0002  '     ,    '蒲小琴    '    ,    '2006-10-26 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417330011        ' ,    '0417340001        ' ,    1000        ,    '0002  '     ,    '蒲小琴    '    ,    '2006-10-26 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417340012        ' ,    '0417350002        ' ,    1000        ,    '0002  '     ,    '蒲小琴    '    ,    '2006-10-26 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417350013        ' ,    '0417360003        ' ,    1000        ,    '0002  '     ,    '蒲小琴    '    ,    '2006-10-26 00:00:00.000' ,    0           ,    '1'  )
insert Tick_IN(MSN,TS_SN,PJBM,in_StartNo,in_EndNo,in_Count,in_Emp,in_EmployeeNo,in_Time,LastCount,Flag) values( NULL        ,    'T01' ,    '2201' ,    '0417360014        ' ,    '0417370004        ' ,    1000        ,    '0002  '     ,    '蒲小琴    '    ,    '2006-10-26 00:00:00.000' ,    0           ,    '1'  )

Answer 8

create function fn_test(@startNO varchar(10))
returns  varchar(10)
AS
begin  declare @t  table (endNO varchar(10),level int)
  declare @level int  set @level=0  insert into @t select  In_endNO,@level from Tick_in where IN_startNO=@startNOwhile @@rowcount>0
begin
   set @level=@level+1
   insert into @t  select a.IN_endNO,@level
                    from Tick_in a,@t b
                    where convert(int ,substring(a.IN_startNO,1,9))=convert(int,substring(b.endNO,1,9))+1
                    and b.level=@level-1
enddeclare @str varchar(10)
select @str=max(endNO) from @t
return @str
endGOselect min(In_startNO) as startNO,endNO,sum(In_Count) as num from 
(select In_startNO,dbo.fn_test(In_startNO) as endNO ,In_Count  
from Tick_in
) A
group by endNO

Answer 9

create function fn_test(@startNO varchar(10))是个自定义函数

Answer 10

用sql2005比较简洁with cte(starttickno,endtickno,hj,nopath,lvl)
 as (select starttickno,endtickno,tickcount,cast(starttickno+'.' as varchar(900)),0 from test union all
 select test.starttickno,test.endtickno,test.tickcount+cte.hj,cast(cte.nopath+test.starttickno+'.' as varchar(900)),cte.lvl+1 from test
 join cte on cast(test.starttickno as int)=cast(cte.endtickno as int)+1)
select starttickno=left(cte.nopath,9),cte.endtickno,hj from cte,
(select fno=starttickno,mhj=max(hj) from cte group by starttickno)as a,
(select fpath=left(nopath,10),mlvl=max(lvl) from cte group by left(nopath,10)) as b
 where starttickno=a.fno 
and left(cte.nopath,10)=b.fpath 
and cte.hj=a.mhj 
and cte.lvl=b.mlvl
 order by starttickno

Answer 11

declare @t1 table(id int identity(1,1),in_StartNo char(18),in_EndNo char(18))
declare @t2 table(id int identity(1,1),in_StartNo char(18),in_EndNo char(18))
insert into @t1
select in_StartNo,in_EndNo from Tick_IN a
where not exists(select * from Tick_IN where cast(in_EndNo as int)+1=cast(a.in_StartNo as int))
insert into @t2
select in_StartNo,in_EndNo from Tick_IN b
where not exists(select * from Tick_IN where cast(in_StartNo as int)-1=cast(b.in_EndNo as int))select a.in_StartNo,b.in_EndNo,cast(b.in_EndNo as int)-cast(a.in_StartNo as int) in_Count
from @t1 a,@t2 b where a.id=b.id

Answer 12

假如StartTickNo 是主键的话 应该用一个O(N)循环搞定了

Answer 13

咸觉每天处理一次的话，总不是办法,那会像滚雪球一样，越来越大。
StartTickNo应该是递增的吧，
那么就可以把 每天处理到最后的StartTickNo的值记录下来，
每天的处理结果也找个表存下来，这样处理起来那就相当于只处理增量数据

Answer 14

还是没有找到好办法.现在还是在做loop

Answer 15

最好还是弄个触发器 然后把你的表插入到类似视图表的 真实表B（就是一般的表 但是作为A表已经整理好的数据）每次插入A表会自动相应触发器 根据 StartTickNo          EndTickNo 的值 来做 插入或者更新B的数据