# sql时间 问题 急！！！！！！！！！！！！！！！

0

ID        Speed            InsrtTime

1         10000.00       2012-12-12 13:14:23

2         0.00              2012-12-12 13:14:28

3         0.00                2012-12-12 13:14:32

4        0.00                 2012-12-12 13:14:37

5        30                  2012-12-12 13:14:42

6        0.00                 2012-12-12 13:14:47

7        90                       2012-12-12 13:14:53

8        0.00                 2012-12-12 13:14:58

9        0.00                  2012-12-12 13:15:03

StartTime                        Endtime

2012-12-12 13:14:28     2012-12-12 13:14:37

2012-12-12 13:14:58    2012-12-12 13:15:03

0

if object_id('TEST') >0 drop table test
create table test
(
id int ,
speed varchar(20),
insrttime datetime
)
insert test
select 1 ,'10000.00','2012-12-12 13:14:23'
union select 2,'0.00','2012-12-12 13:14:28'
union select 3,'0.00','2012-12-12 13:14:32'
union select 4,'0.00','2012-12-12 13:14:37'
union select 5,'30','2012-12-12 13:14:42'
union select 6,'0.00','2012-12-12 13:14:47'
union select 7,'90','2012-12-12 13:14:53'
union select 8,'0.00','2012-12-12 13:14:58'
union select 9,'0.00','2012-12-12 13:15:03'

select * into #TEMP6 from test where speed = '0.00' order by id
---查询---
/*你这个语句就是找孤岛的起始点。他的意思就是当前值-1后在表中不存在的。

select id,insrttime,tid=identity(int,1,1) into #1 from #TEMP6 t
where not exists(select * from #TEMP6 where id=t.id-1)
/*孤島的結束點*/
select id,insrttime,tid=identity(int,1,1) into #2 from #TEMP6 t
where not exists(select * from #TEMP6 where id=t.id+1)
select a.insrttime,b.insrttime
from #1 a
inner join #2 b on a.tid = b.tid and a.id <> b.id
drop table #TEMP6
drop table #1
drop table #2

maanshancss | 菜鸟二级 |园豆：303 | 2012-12-21 08:58

select a.StoreCode（是根据a.storeid = b.storeid）,StartTime，EndTime(这两个是上面的，是Traffic表里面的)

from Store a
left join Router b on a.storeid = b.storeid
left join RouterNet c on b.routerid = c.routerid
left join Traffic d on c.NetGateId = d.NetGateId
where c.Type = '2'

@夏忆（夏天的回忆）:

View Code
```select a.insrttime,b.insrttime
from #1 a
inner join #2 b on a.tid = b.tid and a.id <> b.id```

maanshancss | 园豆：303 (菜鸟二级) | 2012-12-21 12:40

0

Rich.T | 园豆：3440 (老鸟四级) | 2012-12-21 10:24

您需要登录以后才能回答，未注册用户请先注册