SQL Server 查询符合会议间隔空闲时长要求的学生及LINQ转换方法

一、SQL Server 查询实现 #

实现思路 #

1. 对每个学生的所有会议按StartTime升序排序，使用LEAD窗口函数获取该学生下一场会议的开始时间
2. 计算当前会议结束时间与下一场会议开始时间的差值，得到两场会议之间的空闲时长
3. 筛选出存在至少一次空闲时长达到要求的学生（如果需要所有相邻会议都满足要求，调整聚合条件即可）
4. 边界场景兼容：无会议的学生默认所有时段空闲、仅有1场会议的学生除会议时段外全部空闲，可根据业务需求调整过滤条件

代码示例 #

-- 定义要求的最低空闲时长，单位：小时，可按需调整
DECLARE @RequiredIdleHours DECIMAL(4,2) = 2.0;

WITH StudentMeetingOrder AS (
    SELECT 
        m.StudentId,
        s.Name,
        s.Surname,
        m.StartTime,
        m.EndTime,
        -- 取同个学生下一场会议的开始时间
        LEAD(m.StartTime) OVER (PARTITION BY m.StudentId ORDER BY m.StartTime) AS NextMeetingStartTime
    FROM Meetings m
    LEFT JOIN Students s ON m.StudentId = s.ID
)
SELECT DISTINCT StudentId, Name, Surname
FROM StudentMeetingOrder
WHERE 
    -- 相邻会议空闲达标
    DATEDIFF(MINUTE, EndTime, NextMeetingStartTime) >= @RequiredIdleHours * 60
    -- 兼容仅1场会议的学生，若不需要可删除该条件
    OR NextMeetingStartTime IS NULL
-- 若需要包含完全没有会议的学生，追加UNION逻辑：
-- UNION
-- SELECT ID AS StudentId, Name, Surname FROM Students WHERE ID NOT IN (SELECT DISTINCT StudentId FROM Meetings)

二、LINQ 查询实现 #

分两种写法，适配不同EF Core版本：

1. EF Core 7.0+ 兼容窗口函数写法（性能最优，和上述SQL逻辑一致） #

// 传入的要求空闲时长，单位小时
decimal requiredIdleHours = 2.0;
var requiredIdleMinutes = (int)(requiredIdleHours * 60);

var result = await dbContext.Meetings
    .OrderBy(m => m.StartTime)
    .GroupBy(m => m.StudentId)
    .Select(g => new
    {
        StudentId = g.Key,
        g.First().Student.Name,
        g.First().Student.Surname,
        HasQualifiedIdle = g.Any(m => 
            EF.Functions.DateDiffMinute(m.EndTime, 
                g.OrderBy(x => x.StartTime).SkipWhile(x => x.Id != m.Id).Skip(1).Select(x => x.StartTime).FirstOrDefault()
            ) >= requiredIdleMinutes
            // 兼容仅1场会议的学生
            || g.Count() == 1
        )
    })
    .Where(x => x.HasQualifiedIdle)
    // 如果要包含无会议的学生，追加Concat逻辑
    // .Concat(dbContext.Students.Where(s => !s.Meetings.Any()).Select(s => new { s.ID as StudentId, s.Name, s.Surname, HasQualifiedIdle = true }))
    .Select(x => new { x.StudentId, x.Name, x.Surname })
    .Distinct()
    .ToListAsync();

2. 低版本EF 兼容写法（先分组后内存计算，适合数据量小的场景） #

// 先加载所有关联数据
var allStudentsWithMeetings = await dbContext.Students
    .Include(s => s.Meetings)
    .ToListAsync();

decimal requiredIdleHours = 2.0;
var requiredIdleMinutes = (int)(requiredIdleHours * 60);

var result = allStudentsWithMeetings
    .Where(s => 
    {
        // 无会议的学生直接符合要求，不需要可删除该条件
        if (!s.Meetings.Any()) return true;
        // 仅1场会议的学生符合要求，不需要可删除该条件
        if (s.Meetings.Count == 1) return true;
        // 排序后遍历相邻会议计算差值
        var orderedMeetings = s.Meetings.OrderBy(m => m.StartTime).ToList();
        for(int i=0; i<orderedMeetings.Count - 1; i++)
        {
            var idleMinutes = (orderedMeetings[i+1].StartTime - orderedMeetings[i].EndTime).TotalMinutes;
            if (idleMinutes >= requiredIdleMinutes)
                return true;
        }
        return false;
    })
    .Select(s => new { s.ID, s.Name, s.Surname })
    .ToList();

注意事项 #

• 所有时间字段需统一时区，避免跨时区计算出现误差
• 若业务要求是所有相邻会议的空闲时长都达标，仅需将上述判断逻辑中的Any改为All，同时调整边界条件即可
• 大表场景下建议给Meetings.StudentId和Meetings.StartTime加联合索引提升查询性能

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