安排日期的行

这相当复杂。这是使用窗口函数的一种方法。

首先，使用日期表枚举没有周末的日期（如果需要，您也可以休假）。然后，通过使用非等额合并，将周期扩展为每行一天。

然后，您可以使用技巧来确定连续的日期。这个技巧是为每个id生成一个序号，然后从日期的序号中减去它。这是连续几天的常数。最后一步只是一个汇总。

结果查询如下所示：

with d as (      select d.*, row_number() over (order by date) as seqnum      from dates d      where day not in ('Saturday', 'Sunday')     )select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)from (select t.*, ds.seqnum, ds.date,  (d.seqnum - row_number() over (partition by id order by ds.date) ) as grp      from table t joind dson ds.date between t.startdate and t.enddate     ) tgroup by t.id, grp;

编辑：

以下是此SQL Fiddle上的版本：

with d as (      select d.*, row_number() over (order by date) as seqnum      from datetable d      where day not in ('Saturday', 'Sunday')     )select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)from (select t.*, ds.seqnum, ds.date,  (ds.seqnum - row_number() over (partition by id order by ds.date) ) as grp      from (select t.*, 'abc' as id from table1 t) t joind dson ds.dateid between t.startdate and t.enddate     ) tgroup by grp;

我相信这是可行的，但日期表中并未包含所有日期。