sql - 计算 SQL Server 中以前的连续行

Question

我有出勤数据列表，如下所示。现在我正在尝试按特定日期范围 (01/05/2016 – 07/05/2016) 查找数据和总当前列，总当前列将根据以前的当前数据 (P) 计算。假设今天是 04/05/2016。如果一个人的状态为 01,02,03,04 'p'，那么它将显示日期 04-05-2016 总当前 4。



你能帮我从这个结果集中找到总的存在吗？

Answer 1

您可以查看此示例，该示例具有计算先前总和值的逻辑。

declare @t table (employeeid int, datecol date, status varchar(2) )

insert into @t values (10001, '01-05-2016', 'P'),
(10001, '02-05-2016', 'P'),
(10001, '03-05-2016', 'P'),
(10001, '04-05-2016', 'P'),
(10001, '05-05-2016', 'A'),
(10001, '06-05-2016', 'P'),
(10001, '07-05-2016', 'P'),
(10001, '08-05-2016', 'L'),
(10002, '07-05-2016', 'P'),
(10002, '08-05-2016', 'L')

--select * from @t 

select * ,  
    SUM(case when status = 'P' then 1 else 0 end) OVER (PARTITION BY   employeeid ORDER BY employeeid, datecol
    ROWS BETWEEN UNBOUNDED PRECEDING 
      AND current row) 
from 
@t

同样的事情通过 cte 的另一个转折(当你编写 SQLSERVER2012 时，下面的解决方案仅适用于 Sqlserver 2012 及更高版本)

;with cte as
(
    select  employeeid , datecol , ROW_NUMBER() over(partition by employeeid order by employeeid, datecol) rowno
    from 
    @t where status = 'P'
)
select t.*, cte.rowno , 
    case when ( isnull(cte.rowno, 0) = 0) 
    then LAG(cte.rowno) OVER (ORDER BY t.employeeid, t.datecol) 
    else cte.rowno
    end LagValue 
from @t t left join cte on t.employeeid = cte.employeeid and t.datecol = cte.datecol
order by t.employeeid, t.datecol