这个需求在HR报表里太常见了——把分散的每日缺勤记录合并成连续的缺勤周期，完全不用游标就能搞定！下面我分SQL、Linq to SQL和存储过程三种方案给你详细说明，同时兼顾周末和法定节假日的排除：

核心思路是：给每条缺勤记录标记所属的「连续区间分组ID」，同一连续工作日缺勤的记录会被分到同一个组，最后按组聚合就能得到单条连续缺勤记录。

首先你需要维护一个法定节假日表（比如叫Holidays，仅需一个HolidayDate字段存储节假日日期），用来排除非工作日的缺勤判定。

完整SQL代码 #

-- 先创建计算工作日间隔的自定义函数（排除周末+节假日）
CREATE FUNCTION dbo.WorkdayDiff(@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @Workdays INT
    -- 第一步：计算两个日期之间的总天数，减去周末数量
    SET @Workdays = DATEDIFF(DAY, @StartDate, @EndDate) 
        - (DATEDIFF(WEEK, @StartDate, @EndDate) * 2)
        - CASE WHEN DATEPART(WEEKDAY, @StartDate) = 1 THEN 1 ELSE 0 END
        + CASE WHEN DATEPART(WEEKDAY, @EndDate) = 1 THEN 1 ELSE 0 END;
    
    -- 第二步：减去期间的工作日节假日数量
    SET @Workdays = @Workdays - (
        SELECT COUNT(*) 
        FROM Holidays 
        WHERE HolidayDate BETWEEN @StartDate AND @EndDate
          AND DATEPART(WEEKDAY, HolidayDate) NOT IN (1,7) -- 避免重复减去周末节假日
    )
    
    RETURN @Workdays
END
GO

-- 生成连续缺勤报告的查询
WITH AbsenceWithPrevDate AS (
    SELECT 
        EmployeeId,
        AbsenceDate,
        Duration,
        -- 获取同一员工的上一条缺勤日期
        LAG(AbsenceDate) OVER (PARTITION BY EmployeeId ORDER BY AbsenceDate) AS PrevAbsenceDate
    FROM Absences
    -- 先过滤掉节假日的记录（如果原数据可能包含的话）
    WHERE AbsenceDate NOT IN (SELECT HolidayDate FROM Holidays)
),
AbsenceWithGroupId AS (
    SELECT 
        *,
        -- 生成分组ID：当是第一条记录，或与上一条缺勤间隔不是1个工作日时，新建分组
        SUM(CASE 
            WHEN PrevAbsenceDate IS NULL 
                OR dbo.WorkdayDiff(PrevAbsenceDate, AbsenceDate) <> 1 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY EmployeeId ORDER BY AbsenceDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupId
    FROM AbsenceWithPrevDate
)
SELECT 
    EmployeeId,
    MIN(AbsenceDate) AS AbsenceStartDate,
    MAX(AbsenceDate) AS AbsenceEndDate,
    SUM(Duration) AS TotalDuration
FROM AbsenceWithGroupId
GROUP BY EmployeeId, GroupId
ORDER BY EmployeeId, AbsenceStartDate;

逻辑说明 #

1. LAG()窗口函数用来获取当前员工的上一条缺勤日期，作为连续判定的参照。
2. WorkdayDiff函数专门计算两个日期之间的有效工作日数，排除周末和节假日，确保连续判定的准确性。
3. 用累加的方式生成GroupId，同一连续区间的记录会得到相同的ID，最后按ID聚合即可得到合并后的缺勤记录。

如果你的项目用Linq to SQL做ORM，可以在代码层面实现类似逻辑。注意：如果数据量很大，建议优先用SQL方案，减少内存处理压力。

代码示例 #

// 先从数据库获取过滤后的缺勤记录（排除节假日）
var filteredAbsences = db.Absences
    .Where(a => !db.Holidays.Any(h => h.HolidayDate == a.AbsenceDate))
    .OrderBy(a => a.EmployeeId)
    .ThenBy(a => a.AbsenceDate)
    .ToList();

// 按员工分组，生成连续缺勤的分组ID
var groupedAbsences = filteredAbsences
    .GroupBy(a => a.EmployeeId)
    .Select(employeeGroup => {
        var sortedAbsences = employeeGroup.OrderBy(a => a.AbsenceDate).ToList();
        int currentGroup = 1;
        var groupedRecords = new List<dynamic>();
        DateTime? lastAbsenceDate = null;

        foreach (var abs in sortedAbsences)
        {
            if (lastAbsenceDate.HasValue)
            {
                // 计算两个日期的有效工作日间隔
                var workdayGap = CalculateWorkdayDiff(lastAbsenceDate.Value, abs.AbsenceDate, db.Holidays.ToList());
                if (workdayGap != 1)
                {
                    currentGroup++;
                }
            }
            groupedRecords.Add(new {
                EmployeeId = employeeGroup.Key,
                AbsenceDate = abs.AbsenceDate,
                Duration = abs.Duration,
                GroupId = currentGroup
            });
            lastAbsenceDate = abs.AbsenceDate;
        }
        return groupedRecords;
    })
    .SelectMany(r => r)
    // 按员工+分组ID聚合，得到最终报告
    .GroupBy(r => new { r.EmployeeId, r.GroupId })
    .Select(g => new {
        EmployeeId = g.Key.EmployeeId,
        AbsenceStartDate = g.Min(r => r.AbsenceDate),
        AbsenceEndDate = g.Max(r => r.AbsenceDate),
        TotalDuration = g.Sum(r => r.Duration)
    })
    .OrderBy(a => a.EmployeeId)
    .ThenBy(a => a.AbsenceStartDate)
    .ToList();

// 辅助计算工作日间隔的方法
private int CalculateWorkdayDiff(DateTime start, DateTime end, List<Holiday> holidays)
{
    int totalDays = (end - start).Days;
    // 计算期间的周末数量
    int weekends = (totalDays / 7) * 2;
    if (start.DayOfWeek == DayOfWeek.Sunday) weekends++;
    if (end.DayOfWeek == DayOfWeek.Saturday) weekends++;
    
    int workdays = totalDays - weekends;
    // 减去期间的工作日节假日
    int holidayCount = holidays.Count(h => 
        h.HolidayDate >= start && h.HolidayDate <= end 
        && h.HolidayDate.DayOfWeek != DayOfWeek.Saturday 
        && h.HolidayDate.DayOfWeek != DayOfWeek.Sunday);
    
    return workdays - holidayCount;
}

如果需要让HR直接调用（或者集成到报表工具），可以把SQL逻辑封装成存储过程：

CREATE PROCEDURE GetContinuousAbsenceReports
    @StartDate DATE = NULL, -- 可选：指定查询起始日期
    @EndDate DATE = NULL,
    @EmployeeId INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    WITH AbsenceWithPrevDate AS (
        SELECT 
            EmployeeId,
            AbsenceDate,
            Duration,
            LAG(AbsenceDate) OVER (PARTITION BY EmployeeId ORDER BY AbsenceDate) AS PrevAbsenceDate
        FROM Absences
        WHERE AbsenceDate NOT IN (SELECT HolidayDate FROM Holidays)
          AND (@StartDate IS NULL OR AbsenceDate >= @StartDate)
          AND (@EndDate IS NULL OR AbsenceDate <= @EndDate)
          AND (@EmployeeId IS NULL OR EmployeeId = @EmployeeId)
    ),
    AbsenceWithGroupId AS (
        SELECT 
            *,
            SUM(CASE 
                WHEN PrevAbsenceDate IS NULL 
                    OR dbo.WorkdayDiff(PrevAbsenceDate, AbsenceDate) <> 1 
                THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY EmployeeId ORDER BY AbsenceDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupId
        FROM AbsenceWithPrevDate
    )
    SELECT 
        EmployeeId,
        MIN(AbsenceDate) AS AbsenceStartDate,
        MAX(AbsenceDate) AS AbsenceEndDate,
        SUM(Duration) AS TotalDuration
    FROM AbsenceWithGroupId
    GROUP BY EmployeeId, GroupId
    ORDER BY EmployeeId, AbsenceStartDate;
END

调用时直接执行：EXEC GetContinuousAbsenceReports @StartDate = '2020-02-01', @EndDate = '2020-02-28';

• 节假日表维护：一定要定期更新Holidays表，否则连续缺勤的判定会出现错误。
• 数据预处理：如果原数据中包含周末的缺勤记录，一定要提前过滤，避免干扰连续区间的判定。
• 性能优化：如果缺勤数据量很大，建议给Absences表的EmployeeId和AbsenceDate字段建立联合索引，提升窗口函数的执行效率。

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