declare @table table(id int ,rq datetime)
insert @table
select 44, '2003-12-7 1:43:11'
union all
select 45, '2003-12-7 2:16:47'
union all
select 46, '2003-12-7 2:19:51'
union all
select 47, '2003-12-7 2:22:56'
union all
select 48, '2003-12-7 7:54:31'
union all
select 49, '2003-12-7 7:57:37'
union all
select 50, '2003-12-7 8:00:43'
union all
select 51, '2003-12-7 8:03:47'
union all
select 52, '2003-12-7 8:06:24'
union all
select 53, '2003-12-7 8:07:27'
union all
select 54, '2003-12-7 8:10:35'
union all
select 55, '2003-12-7 8:11:28'
union all
select 56, '2003-12-7 8:11:28'
union all
select 57, '2003-12-7 8:14:08'
union all
select 58, '2003-12-7 8:16:02'
union all
select 59, '2003-12-7 8:18:51'
union all
select 60, '2003-12-7 8:21:38'
union all
select 61, '2003-12-7 8:21:38'select * ,datediff(mi,a.rq,b.rq) [时间间隔] from @table a join @table b
on a.id = b.id -1
where datediff(mi,a.rq,b.rq) > 10select '累计次数:' + cast(@@rowcount as varchar) + '次'id rq id rq 时间间隔
---- --------------------------- --------------------------- -----
44 2003-12-07 01:43:11.000 45 2003-12-07 02:16:47.000 33
47 2003-12-07 02:22:56.000 48 2003-12-07 07:54:31.000 332(所影响的行数为 2 行)
-----------------------------------------
累计次数:2次(所影响的行数为 1 行)
insert @table
select 44, '2003-12-7 1:43:11'
union all
select 45, '2003-12-7 2:16:47'
union all
select 46, '2003-12-7 2:19:51'
union all
select 47, '2003-12-7 2:22:56'
union all
select 48, '2003-12-7 7:54:31'
union all
select 49, '2003-12-7 7:57:37'
union all
select 50, '2003-12-7 8:00:43'
union all
select 51, '2003-12-7 8:03:47'
union all
select 52, '2003-12-7 8:06:24'
union all
select 53, '2003-12-7 8:07:27'
union all
select 54, '2003-12-7 8:10:35'
union all
select 55, '2003-12-7 8:11:28'
union all
select 56, '2003-12-7 8:11:28'
union all
select 57, '2003-12-7 8:14:08'
union all
select 58, '2003-12-7 8:16:02'
union all
select 59, '2003-12-7 8:18:51'
union all
select 60, '2003-12-7 8:21:38'
union all
select 61, '2003-12-7 8:21:38'select * ,datediff(mi,a.rq,b.rq) [时间间隔] from @table a join @table b
on a.id = b.id -1
where datediff(mi,a.rq,b.rq) > 10select '累计次数:' + cast(@@rowcount as varchar) + '次'id rq id rq 时间间隔
---- --------------------------- --------------------------- -----
44 2003-12-07 01:43:11.000 45 2003-12-07 02:16:47.000 33
47 2003-12-07 02:22:56.000 48 2003-12-07 07:54:31.000 332(所影响的行数为 2 行)
-----------------------------------------
累计次数:2次(所影响的行数为 1 行)
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;
select case when [时间间隔] > 10 and [时间间隔] < 60 then 'A'
when [时间间隔] > 60 and [时间间隔] < 120 then 'B'
else 'C' end [时间间隔],
count(*)
from
(
select a.id,a.rq rq1,b.rq rq2, datediff(mi,a.rq,b.rq) [时间间隔] from @table a join @table b
on a.id = b.id -1
) a
group by
case when [时间间隔] > 10 and [时间间隔] < 60 then 'A'
when [时间间隔] > 60 and [时间间隔] < 120 then 'B'
else 'C' 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
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几个啊。是不是我哪里没对上?