declare cursor test is select 你的时间列 from table1;
temp1 date;
temp2 date;
temp3 int;
begin
temp3:=1;
open test;
loop
fetch test into temp1;
if (to_char((temp1-temp2)*24*60)>10) then
temp3:=temp3+1;
end if;
temp2:=temp1;
exit when test%notfound;
end loop;
close test;
dbms_output.put_line(temp3);
commit;
end;
temp1 date;
temp2 date;
temp3 int;
begin
temp3:=1;
open test;
loop
fetch test into temp1;
if (to_char((temp1-temp2)*24*60)>10) then
temp3:=temp3+1;
end if;
temp2:=temp1;
exit when test%notfound;
end loop;
close test;
dbms_output.put_line(temp3);
commit;
end;
temp1 date;
temp2 date;
temp3 int;
begin
temp3:=0;
temp2:=to_date('1900-01-01 01:00:00','yyyy-mm-dd hh:mi:ss');
open test;
loop
fetch test into temp1;
if (to_char((temp1-temp2)*24*60)>10) then
temp3:=temp3+1;
temp2:=temp1;
end if;
exit when test%notfound;
end loop;
close test;
dbms_output.put_line(temp3);
commit;
end;
--测试数据
declare @t table(id varchar(40),dt datetime)
insert into @t(id,dt)
select '044f5e65-a716-4543-809a-768a8326f55f','2003-12-7 1:43:11'
union all select 'af74e2dd-8fd9-4008-9ff6-4c7e65fed1e6','2003-12-7 2:16:47'
union all select '004e9d29-6904-4671-af3d-65892874c706','2003-12-7 2:19:51'
union all select '18e5a7fe-1a98-4942-8251-24eb630cdd4a','2003-12-7 2:22:56'
union all select '2ebcae78-14ec-4711-9fd7-47ab2e7f8193','2003-12-7 7:54:31'
union all select 'd1d4409a-aad6-48bd-a765-2c709b3fa20d','2003-12-7 7:57:37'
union all select '981d4c8b-9404-4bcc-afa5-23d345d59dfe','2003-12-7 8:00:43'
union all select '0e6587d8-0219-45e4-82af-c9a2fbdd364b','2003-12-7 8:03:47'
union all select 'e27d7ef1-42ae-4fdf-b218-79f15bd53c80','2003-12-7 8:06:24'
union all select 'd02992d8-4e1c-45be-86f1-f0af2b6f6d20','2003-12-7 8:07:27'
union all select 'ea901340-d18c-4b3b-bf21-53f5705374be','2003-12-7 8:10:35'
union all select '3257e765-c561-48ef-83b2-e72f525f3b46','2003-12-7 8:11:28'
union all select '378800b9-bf80-4770-9b54-212f2bad9699','2003-12-7 8:11:28'
union all select 'cc2870e7-9656-4131-a36d-649afdd80d3d','2003-12-7 8:14:08'
union all select 'f9b96e14-a843-4e90-b724-64e56d0863d6','2003-12-7 8:16:02'
union all select '9cfb12b9-f0a9-465f-800c-3e229da46ddb','2003-12-7 8:18:51'
union all select '7eca3caf-9c7c-4afc-a961-2c17c6669ff5','2003-12-7 8:21:38'
union all select '9fab0118-8cc5-4c9a-a2a4-2b5fe284eacd','2003-12-7 8:21:38'--查询
select count(*) from(
select * from @t a
where not exists(select 1 from @t where id<>a.id and dt>=a.dt and datediff(ss,a.dt,dt)<=600)
) a
from table t,table t2
where t.dt>t2.dt
having convert(float,(t.dt-max(t2.dt)) >=(1.0000000/1440)*10 --取得10分钟怎么表示
是一个获得间隔的视图
前几天想过类似的:
select t.id,(t.dt-max(t2.dt)) as 间隔
from table t,table t2
where t.dt>t2.dt
group by t.id,t.dt
having cast((t.dt-max(t2.dt)as float) >=(1.0000000/1440)*10 --取得10分钟怎么表示
是一个获得间隔的视图
declare cursor test is select MSGTIME from TAXI.TAXI_CAR_POSI_HISTORY A WHERE --A.SPEED=0 AND
A.CARID=120
AND A.MSGTIME >= to_date('2003-12-7 00:00:01','yyyy-mm-dd hh24:mi:ss')
AND MSGTIME <= to_date('2003-12-7 23:59:59','yyyy-mm-dd hh24:mi:ss')
ORDER BY A.MSGTIME;
temp1 date;
temp2 date;
temp3 int;
begin
temp3:=0;
temp2:=to_date('1900-01-01 01:00:00','yyyy-mm-dd hh:mi:ss');
open test;
loop
fetch test into temp1;
if (to_char((temp1-temp2)*24*60)>10) then
dbms_output.put_line(to_char(temp1-temp2));
temp3:=temp3+1;
temp2:=temp1;
end if;
exit when test%notfound;
end loop;
close test;
dbms_output.put_line(temp3);
commit;
end;
------------------------------------------
得到的输出是:
37959.9593634259259259259259259259259259
.007326388888888888888888888888888888888889
.007268518518518518518518518518518518518519
.008738425925925925925925925925925925925926
.009097222222222222222222222222222222222222
.007881944444444444444444444444444444444444
.008541666666666666666666666666666666666667
.008969907407407407407407407407407407407407
.0106597222222222222222222222222222222222
.025474537037037037037037037037037037037
.234537037037037037037037037037037037037
.008252314814814814814814814814814814814815
.008645833333333333333333333333333333333333
.007071759259259259259259259259259259259259
.00962962962962962962962962962962962962963
.008263888888888888888888888888888888888889
.008645833333333333333333333333333333333333
.00724537037037037037037037037037037037037
.007523148148148148148148148148148148148148
.008206018518518518518518518518518518518519
.007152777777777777777777777777777777777778
.009861111111111111111111111111111111111111
.00787037037037037037037037037037037037037
.007118055555555555555555555555555555555556
.008865740740740740740740740740740740740741
.0175231481481481481481481481481481481481
.009131944444444444444444444444444444444444
.008935185185185185185185185185185185185185
.007719907407407407407407407407407407407407
.007210648148148148148148148148148148148148
.008634259259259259259259259259259259259259
.009930555555555555555555555555555555555556
.00869212962962962962962962962962962962963
.008483796296296296296296296296296296296296
.007708333333333333333333333333333333333333
.00818287037037037037037037037037037037037
.007673611111111111111111111111111111111111
.0101967592592592592592592592592592592593
.007326388888888888888888888888888888888889
.007638888888888888888888888888888888888889
.008761574074074074074074074074074074074074
.007303240740740740740740740740740740740741
.007152777777777777777777777777777777777778
.007731481481481481481481481481481481481481
.008148148148148148148148148148148148148148
.0125115740740740740740740740740740740741
.0140509259259259259259259259259259259259
.0195717592592592592592592592592592592593
.0205555555555555555555555555555555555556
.007951388888888888888888888888888888888889
.008368055555555555555555555555555555555556
.008576388888888888888888888888888888888889
.008506944444444444444444444444444444444444
.009722222222222222222222222222222222222222
.009560185185185185185185185185185185185185
.0102662037037037037037037037037037037037
.009189814814814814814814814814814814814815
.008761574074074074074074074074074074074074
.008796296296296296296296296296296296296296
.007256944444444444444444444444444444444444
.009259259259259259259259259259259259259259
.0224884259259259259259259259259259259259
.007361111111111111111111111111111111111111
.007523148148148148148148148148148148148148
.007303240740740740740740740740740740740741
.00787037037037037037037037037037037037037
.007141203703703703703703703703703703703704
.006956018518518518518518518518518518518519
.007581018518518518518518518518518518518519
.007800925925925925925925925925925925925926
.006967592592592592592592592592592592592593
.008310185185185185185185185185185185185185
.0162847222222222222222222222222222222222
.0115509259259259259259259259259259259259
.009363425925925925925925925925925925925926
.008877314814814814814814814814814814814815
.0354861111111111111111111111111111111111
.0136574074074074074074074074074074074074
.0127083333333333333333333333333333333333
79
-----------------------------------
而实际上我看了一下,应该是只有10几个啊
select t.id,(t.dt-max(t2.dt)) as 间隔
from table t,table t2
where t.dt>t2.dt
group by t.id,t.dt
having cast((t.dt-max(t2.dt))as float) >=(1.0000000/1440)*10 --取得10分钟怎么表示