SQL如何按但有特殊条件进行分组

Question

我有一个SQL Server 2008表，其中有一个具有timestamps的员工列表。

我有一个脚本，它按照员工的日期分组。

我需要的是按员工分组，但我必须排除同一天的时间戳，两者之间的差异是小于8小时。

以下是一张解释得更好的表格：

​

​

我用表和示例数据创建了一个SQL。

http://sqlfiddle.com/#!3/3b956/1

有线索吗？

Answer 1

您真正需要的是lag()，它位于Server 2012+中。使用lag()，您可以这样做：

select t.*
from (select t.*, lag(date) over (partition by EmployeeId order by date) as prev_date
      from t
     ) t
where not (cast(prev_date as date) = cast(date as date) and
           date <= dateadd(hour, 8, prev_date)
          ) or
      prev_date is null;

在Server 2008中，您可以使用outer apply执行类似的操作

select t.*
from t outer apply
     (select top 1 prev.*
      from t prev
      where prev.Employee_id = t.EmployeeId and
            prev.date < t.date and
            cast(prev.date as date) = cast(t.date as date)
      order by prev.date desc
     ) prev
where prev.date is null or
      t.date > dateadd(hour, 8, prev.date);

您可能需要一个order by来维持相同的顺序。

Answer 2

这还应排除存在差异小于8小时的先前行的行：

select p1.employeeid, count(*) as [count]
from punch p1
where not exists(select * from punch p2 
                 where p2.employeeid = p1.employeeid and p2.id < p1.id and
                 dateadd(hour, 8, p2.date) > p1.date)
group by p1.employeeid