如果序列未中断，则从多行获取总时间间隔

可能有一种方法可以在一个SQL选择中执行此 *** 作，但是它使我无所适从。我设法做到了一个存储功能。这是我所做的测试：

create table work(id integer, start_date date, end_date date);insert into work values (1, '2011-01-01','2011-02-02');insert into work values (1, '2011-02-02','2011-04-04');insert into work values (1, '2011-06-06','2011-09-09');insert into work values (2, '2011-01-01','2011-02-02');insert into work values (2, '2011-02-02','2011-03-03');create or replace function get_data() returns setof work as$body$declare    res work%rowtype;    sd  date := null;begin    for res in        select w1.id, case when exists (select 1 from work w2 where w1.id=w2.id and w2.end_date=w1.start_date) then null else w1.start_date end, case when exists (select 1 from work w2 where w1.id=w2.id and w2.start_date=w1.end_date) then null else w1.end_date end        from work w1        order by id, start_date, end_date    loop        if res.start_date is not null and res.end_date is not null then return next res;        elsif res.start_date is not null then sd := res.start_date;        elsif res.end_date is not null then res.start_date := sd; return next res;        end if;    end loop;    return;end;$body$  language 'plpgsql';

然后

select * from get_data() order by id, start_date;

返回此结果：

 id | start_date |  end_date----+------------+------------  1 | 2011-01-01 | 2011-04-04  1 | 2011-06-06 | 2011-09-09  2 | 2011-01-01 | 2011-03-03(3 rows)

我想，这就是您所追求的。