如何通过CTE设置变量，自动化删除夜间及异常班次的无效数据？

原代码问题分析 #

你之前的代码无法正确赋值变量，是因为没有用SELECT @变量 = 字段的形式给变量赋值，仅做查询操作的话变量并不会拿到预期的ID值。另外重复定义CTE属于冗余操作，可以合并为一次查询获取最小和最大ID。

修正后的变量赋值方案 #

如果坚持要用ID范围删除，可通过以下代码一次性获取当日7:45前记录的最小/最大ID，再执行删除：

DECLARE @Min_id AS INT;
DECLARE @Max_id AS INT;

WITH CTE AS (
  SELECT
    id,
    CAST(StartTime AS TIME) AS Time
  FROM ChangeoverTime 
  WHERE StartTime >= CAST(GETDATE() AS DATE) -- 简化当日数据筛选逻辑
)
SELECT 
  @Min_id = MIN(id),
  @Max_id = MAX(id)
FROM CTE
WHERE Time < '07:45:00';

-- 仅当存在符合条件的记录时执行删除
IF @Min_id IS NOT NULL AND @Max_id IS NOT NULL
BEGIN
  DELETE FROM ChangeoverTime
  WHERE id BETWEEN @Min_id AND @Max_id;
END

更稳妥的直接按时间删除方案 #

依赖ID范围存在风险（比如ID不连续、部分符合条件的记录不在ID区间内），建议直接基于StartTime的时间条件删除，逻辑更直观准确：

DELETE FROM ChangeoverTime
WHERE 
  StartTime >= CAST(GETDATE() AS DATE) -- 仅筛选当日数据
  AND CAST(StartTime AS TIME) < '07:45:00'; -- 删除早于7:45的夜间记录

后续扩展：删除400行以上的生产班次数据 #

假设生产班次按连续记录的时间间隔划分（比如相邻记录间隔超过10分钟视为新班次），可通过窗口函数分组统计班次记录数，再删除符合条件的班次数据：

WITH ShiftGroups AS (
  SELECT 
    id,
    -- 标记班次分组：相邻记录间隔超过10分钟则新建一个班次ID
    SUM(CASE WHEN DATEDIFF(minute, LAG(StartTime) OVER (ORDER BY StartTime), StartTime) > 10 THEN 1 ELSE 0 END) OVER (ORDER BY StartTime) AS ShiftId
  FROM ChangeoverTime
),
ShiftCounts AS (
  SELECT 
    ShiftId,
    COUNT(*) AS RecordCount
  FROM ShiftGroups
  GROUP BY ShiftId
  HAVING COUNT(*) >= 400 -- 筛选记录数≥400的班次
)
DELETE ct
FROM ChangeoverTime ct
JOIN ShiftGroups sg ON ct.id = sg.id
JOIN ShiftCounts sc ON sg.ShiftId = sc.ShiftId;

自动化执行 #

把最终确定的SQL脚本配置到SQL Server Agent作业中，设置每日8点自动执行即可完成自动化删除。

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