如何实现sql递归相减

[bukebuhao]

表的结构(海量的数据库,目前有3000万条记录)
ID CarKey Date
1 15022104271 2007-12-11 14:59:00
2 15022104271 2007-12-11 15:00:00
3 15022104271 2007-12-11 15:03:00
23 15022104275 2007-12-11 15:53:00
25 15022104275 2007-12-11 16:05:00
29 15022104275 2007-12-11 16:11:00
通过sql查询得到的结果是

1 15022104271 2007-12-11 14:59:00
2 15022104271 00:01:00
3 15022104271 00:03:00
23 15022104275 2007-12-11 15:53:00
25 15022104275 00:12:00
29 15022104275 00:06:00

有没有比较好的思路,方法,有实现方法最好

[isoftstonesgg]

看不懂查询语句和楼主的意思哦
能麻烦大家解释一下吗？
在下先谢过了

[isoftstonesgg]

没看标题实在不好意思哦

[lixinfa2000]

相當強大

[fcuandy]

写法不是问题，3000w，写起来，这种需求。。


可以考滤一下建立适当索引， 并把子查询写法改为先聚合再内连接。 效率应该有提升，不过能提升多少，用户满不满意，就不确定了。

[chentony]

看高手回答　

[时光瞄]

4楼答得好啊...

[bukebuhao]

有没有高手呢？

[律己修心]

if object_id('tempdb..#tb') is not null drop table #tb
go
create table #tb (ID int ,CarKey varchar(20) ,Date datetime)
go
insert into #tb select 1, '15022104271','2007-12-11 14:59:00' 
union all select 2, '15022104271','2007-12-11 15:00:00' 
union all select 3, '15022104271','2007-12-11 15:03:00' 
union all select 23, '15022104275','2007-12-11 15:53:00' 
union all select 25, '15022104275', '2007-12-11 16:05:00' 
union all select 29, '15022104275','2007-12-11 16:11:00'
go
select ID,CarKey,replace(convert(varchar(20),Date-(select isnull(max(Date),0) from #tb where t.CarKey=CarKey and ID<t.ID),120),'1900-01-01 ','') as Date
from #tb t
/*
ID	CarKey	Date
1	15022104271	2007-12-11 14:59:00
2	15022104271	00:01:00
3	15022104271	00:03:00
23	15022104275	2007-12-11 15:53:00
25	15022104275	00:12:00
29	15022104275	00:06:00
*/

[cxmcxm]

将查询结果存到临时表中,再对临时表的日期进行处理,输出结果

[ChinaJiaBing]

create table aa(id int,carkey varchar(15),date datetime)
insert into aa select 1,'15022104271','2007-12-11 14:59:00'
     union all select 2,'15022104271','2007-12-11 15:00:00'
     union all select 3,'15022104271','2007-12-11 15:03:00'
     union all select 23,'15022104275','2007-12-11 15:53:00'
     union all select 25,'15022104275','2007-12-11 16:05:00'
     union all select 29,'15022104275','2007-12-11 16:11:00'
------------
 select id,carkey,isnull(substring(convert(varchar(20),date-(select min(date)from aa where id<a.id and a.carkey=carkey),112),11,18) ,date) from aa a

[184270428]

declare @t table(ID int,CarKey bigint,Date datetime)
insert into @t values(1 ,15022104271,'2007-12-11 14:59:00')
insert into @t values(2 ,15022104271,'2007-12-11 15:00:00')
insert into @t values(3 ,15022104271,'2007-12-11 15:03:00')
insert into @t values(23,15022104275,'2007-12-11 15:53:00')
insert into @t values(25,15022104275,'2007-12-11 16:05:00')
insert into @t values(29,15022104275,'2007-12-11 16:11:00')

if object_id('tempdb..#t') > 0 drop table #t
declare @odate datetime,@i int,@carkey bigint,@j int
select id,carkey,date,i=0,t='00:00:00' into #t from @t order by carkey

update #t set
@odate = case when @odate is null then date else @odate end,
@carkey = case when @carkey is null then carkey else @carkey end,
@odate = case when @carkey = carkey then @odate else date end,
@i=datediff(s,@odate,date),
@j = i = case when @carkey = carkey then @i - isnull(@j,0) else 0 end,
@carkey = case when @carkey = carkey then @carkey else carkey end,
t = right('00' + cast(((@j/360) % 60) as varchar(2)),2) + ':' + right('00' + cast(((@j/60) % 60) as varchar(2)),2) + ':'+ right('00' + cast((@j % 60) as varchar(2)),2)
select * from #t

[184270428]

粘贴到分析器里就可以执行

[184270428]

declare @t table(ID int,CarKey bigint,Date datetime)
insert into @t values(1 ,15022104271,'2007-12-11 14:59:00')
insert into @t values(2 ,15022104271,'2007-12-11 15:00:00')
insert into @t values(3 ,15022104271,'2007-12-11 15:03:00')
insert into @t values(23,15022104275,'2007-12-11 15:53:00')
insert into @t values(25,15022104275,'2007-12-11 16:05:00')
insert into @t values(29,15022104275,'2007-12-11 16:11:00')

if object_id('tempdb..#t') > 0 drop table #t
declare @odate datetime,@i int,@carkey bigint,@j int
select id,carkey,date,t='00:00:00' into #t from @t order by carkey

update #t set
@odate = case when @odate is null then date else @odate end,
@carkey = case when @carkey is null then carkey else @carkey end,
@odate = case when @carkey = carkey then @odate else date end,
@i=datediff(s,@odate,date),
@j = case when @carkey = carkey then @i - isnull(@j,0) else 0 end,
@carkey = case when @carkey = carkey then @carkey else carkey end,
t = right('00' + cast(@j/3600 % 60 as varchar(2)),2) + ':' + right('00' + cast(((@j/60) % 60) as varchar(2)),2) + ':'+ right('00' + cast((@j % 60) as varchar(2)),2),
@carkey = case when @carkey = carkey then @carkey else carkey end

select * from #t



360少写一个零应该是:3600

[bukebuhao]

现在感觉很差不多/

[bukebuhao]

我现在遇到一个问题，就是只是简单的查询一下数目，目前也就是2068万条记录，就花费了17分钟，数据库是sql2005，我差点要崩溃，目前又添加了一条内存，不需要递减，只是统计有多少不同的卡车carKey，就很难办到

[184270428]

发表于：2008-11-11 10:00:0124楼 得分:0
现在感觉很差不多/

晕昨天试了半天.结果不刚好和你要的一样吗!

我现在遇到一个问题，就是只是简单的查询一下数目，目前也就是2068万条记录，就花费了17分钟，数据库是sql2005，我差点要崩溃，目前又添加了一条内存，不需要递减，只是统计有多少不同的卡车carKey，就很难办到

结合经常要查的条件建索引,还有——这个表有建分区吗？

[bukebuhao]

没有建分区，如何建？

[184270428]

http://www.cnblogs.com/jackyrong/archive/2006/11/13/559354.html

[184270428]

http://www.enet.com.cn/article/2008/0324/A20080324194778.shtml

[bukebuhao]

不胜感激！