在itpub里看到一个很有意思的sql 一个表中记录了各个地点的报警开始时间，结束时间。 现在想统计，各个地点报警次数和时长，时长=结束时间-开始时间。 要求，同一个地点，开始时间间隔超过10分钟的，这个地点要在结果中再出现一次，10分钟之内的假如有多条，统计条数和报警时长，时长=10分钟内各条时长之和。 create table T_DEMO ( id VARCHAR2(32) not null, place_id VARCHAR2(32), s_time DATE, e_time DATE )； insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('1', '1', to_date('21-08-2014 01:24:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 01:24:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('2', '1', to_date('21-08-2014 01:25:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 01:25:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('3', '1', to_date('21-08-2014 01:35:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 01:35:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('4', '1', to_date('21-08-2014 01:39:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 01:39:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('5', '1', to_date('21-08-2014 02:05:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 02:05:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('6', '1', to_date('21-08-2014 02:06:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 02:06:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('7', '1', to_date('21-08-2014 02:12:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 02:12:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('8', '1', to_date('21-08-2014 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 02:14:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('9', '1', to_date('21-08-2014 02:50:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 02:50:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('10', '1', to_date('21-08-2014 02:52:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 02:52:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('11', '1', to_date('21-08-2014 02:57:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('21-08-2014 02:57:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('12', '1', to_date('25-08-2014 09:13:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-08-2014 09:13:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('13', '1', to_date('25-08-2014 09:18:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-08-2014 09:18:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('14', '1', to_date('25-08-2014 09:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-08-2014 09:30:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('15', '1', to_date('25-08-2014 09:37:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-08-2014 09:37:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('16', '1', to_date('25-08-2014 09:47:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-08-2014 09:47:01', 'dd-mm-yyyy hh24:mi:ss')); insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME) values ('17', '2', to_date('25-08-2014 10:09:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-08-2014 10:09:01', 'dd-mm-yyyy hh24:mi:ss')); WITHt2AS (SELECT  a.place_id, a.s_time, a.e_time, a.id, floor((a.s_time - to_date('2000-01-01','YYYY-MM-DD')) * 24 * 60 / 10) last_cha FROM t_demo a) SELECT place_id 地点, SUM(e_time - s_time) * 24 * 60 * 60 "时长:秒", min(s_time)  开始时间， COUNT(*) 次数 FROM t2 GROUP BY place_id, last_cha;