无需使用游标，通过DateDiff更新SQL数据表

我编写的存储过程耗时过长，需求是根据同一VisitorID的其他行数据，更新每行的DaysBetweenVisits列。目前采用双层游标实现：先遍历每个VisitorID，再遍历该VisitorID对应的每个DepartmentID，计算DateDiff结果存入临时表后更新实际数据表。

数据表示例：

存储过程能正常运行，但遍历速度太慢，后续还要针对其他字段实现类似逻辑，希望找到更简便的替代方案（比如子查询或集合式操作）。当前存储过程代码如下：

SET NOCOUNT ON;

create table #VisitDetails
(
DeptID int,
VisitDate date,
DaysBetween int
);

DECLARE @CurrentVisitor bigint;
DECLARE @CurrentDeptID int;
DECLARE @CurrentDate date;
DECLARE @PrevDeptID int;
DECLARE @PrevDate date;
DECLARE @DaysBetween int;

DECLARE Visitor_cursor CURSOR FOR  
SELECT distinct VisitorID
from [dbo].[visits]
;

OPEN Visitor_cursor 
FETCH NEXT FROM Visitor_cursor INTO  @CurrentVisitor;

WHILE @@FETCH_STATUS = 0  
BEGIN
    
    Truncate table #VisitDetails;

    INSERT INTO #VisitDetails (DeptID, VisitDate, DaysBetween)
    SELECT DeptID, VisitDate, 0
    FROM [dbo].[visits] v
    where v.VisitorID = @CurrentVisitor
    order by DeptID, VisitDate;

    set @CurrentDate = '1900-01-01';
    set @PrevDate = '1900-01-01';
    set @DaysBetween = 0;
    set @PrevDeptID = null

    DECLARE DeptID_cursor CURSOR FOR
    SELECT distinct DeptID, VisitDate
    from #VisitDetails;

    OPEN DeptID_cursor
    FETCH NEXT FROM DeptID_cursor into @CurrentDeptID, @CurrentDate;

    WHILE @@FETCH_STATUS = 0
    BEGIN --DeptID
        if @PrevDeptID is not null and @PrevDeptID <> @CurrentDeptID
            set @PrevDate = '1900-01-01'
        
        If @PrevDate = '1900-01-01'
            set @DaysBetween = 0;
        else if @PrevDate is null
            set @DaysBetween = null;
        else
            set @DaysBetween = DATEDIFF(d, @PrevDate, @CurrentDate);

        UPDATE #VisitDetails
        SET DaysBetween = @DaysBetween
        where DeptID = @CurrentDeptID
        and VisitDate = @CurrentDate;

        set @PrevDeptID = @CurrentDeptID;
        set @PrevDate = @CurrentDate;

        --DECLARE @temptable XML = (SELECT * FROM #VisitDetails FOR XML AUTO)

        FETCH NEXT FROM DeptID_cursor into @CurrentDeptID, @CurrentDate;
    END

    UPDATE v
    set v.DaysBetween = t.DaysBetween
    from visits v
    join #VisitDetails t on v.DeptID = t.DeptID and v.VisitDate = t.VisitDate
    where v.VisitorID = @CurrentVisitor
    ;

    close DeptID_cursor;
    deallocate DeptID_cursor;
    FETCH NEXT FROM Visitor_cursor INTO  @CurrentVisitor;
END

CLOSE Visitor_cursor;
DEALLOCATE Visitor_cursor;

游标逐行处理在大数据量场景下性能极差，推荐使用SQL窗口函数LAG()直接实现需求，无需游标和临时表，效率提升显著。

核心逻辑 #

• 按VisitorID和DepartmentID分区，确保仅对比同一访客同一部门的访问记录
• 每个分区内按VisitDate排序，获取上一条记录的访问日期
• 计算当前日期与上一条日期的间隔，分区内第一条记录的间隔设为0

优化后的更新语句 #

SET NOCOUNT ON;

UPDATE v
SET DaysBetweenVisits = ISNULL(DATEDIFF(day, LAG(VisitDate) OVER (PARTITION BY VisitorID, DepartmentID ORDER BY VisitDate), VisitDate), 0)
FROM [dbo].[visits] v;

语句解释 #

• PARTITION BY VisitorID, DepartmentID：将数据按访客ID和部门ID分组，隔离不同访客或部门的访问记录
• ORDER BY VisitDate：每个分组内按访问日期排序，保证LAG()取到的是上一次访问的日期
• LAG(VisitDate)：获取当前行的上一行VisitDate值，分组内第一行该值为NULL
• ISNULL(..., 0)：将第一行的NULL结果转为0，匹配示例中的预期输出

扩展说明 #

后续处理类似“基于上/下一行数据计算当前行”的需求时，都可以用窗口函数高效实现：

• LAG()：获取上一行指定字段的值
• LEAD()：获取下一行指定字段的值
• ROW_NUMBER()：生成分组内的排序编号
  这类集合式操作的执行效率远高于游标逐行遍历。

内容的提问来源于stack exchange，提问作者prollyd