sql - 如果序列未被破坏，则从多行获取总时间间隔

Question

我有Work 和Person 表(这些只是理解问题的示例)。

结构

工作表

id INTEGER
person_id INTEGER
dt_from DATETIME
dt_to DATETIME

人物表

person_id INTEGER
name VARCHAR(50)

数据

工作表

id | person_id | dt_from    | dt_to
-------------------------------------------------
1  | 1         | 2011-01-01 | 2011-02-02
2  | 1         | 2011-02-02 | 2011-04-04
3  | 1         | 2011-06-06 | 2011-09-09
4  | 2         | 2011-01-01 | 2011-02-02
5  | 2         | 2011-02-02 | 2011-03-03
....etc.

人物表

只有人名和人号

预期输出

Person 1 : 2011-01-01 - 2011-04-04
Person 1 : 2011-06-06 - 2011-09-09
Person 2 : 2011-01-01 - 2011-03-03

区间必须有顺序。它不能在中间的某个地方被打破。这就是第 1 个人有两个间隔的原因。

如果它改变了什么，我正在使用 postgres。你有什么想法吗？我想在一个查询中完成，但如果没有这样的解决方案，我将在 php 中进行一些间隔合并。

Answer 1

可能有一种方法可以在一个 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)

我认为这就是您所追求的。