SQL实现：根据子查询日期区间按mach_id删除跨表记录求助

别慌，咱们一步步拆解解决这个问题。你的核心需求很清晰：找到每个mach_id下value1=0的记录的created_on，以及该机器紧接着的下一条记录的created_on，再用这个时间区间去删除另一张表中对应机器且时间落在区间内的数据。

第一步：修正查询，获取正确的时间区间 #

你原来的查询有个关键问题：lead()函数没有按mach_id分区，会跨机器取后续记录的时间，这显然不符合需求。我们先调整查询，精准拿到每个机器对应的时间区间：

SELECT 
    mach_id,
    created_on AS start_time,  -- value1=0时的时间（区间起点）
    lead(created_on) OVER (PARTITION BY mach_id ORDER BY created_on) AS end_time  -- 同机器下一条记录的时间（区间终点）
FROM MyTable 
WHERE 
    field_name = 'someValue' 
    AND CAST(created_on AS DATE) = CAST(GETDATE() AS DATE)
    AND value1 = 0;  -- 只筛选value1为0的记录

这里PARTITION BY mach_id确保我们只在同一机器的记录里找下一条，ORDER BY created_on保证时间顺序正确。如果某个机器的最后一条记录就是value1=0，lead()会返回NULL，后续处理时你可以根据需求决定是否要删除该起点之后的所有数据。

第二步：用时间区间删除目标表数据 #

假设要删除的表叫TargetTable，我们可以用两种常见方式实现删除：

方式一：JOIN关联删除（直观易懂）

DELETE t
FROM TargetTable t
JOIN (
    -- 这里嵌入第一步的区间查询
    SELECT 
        mach_id,
        created_on AS start_time,
        lead(created_on) OVER (PARTITION BY mach_id ORDER BY created_on) AS end_time
    FROM MyTable 
    WHERE 
        field_name = 'someValue' 
        AND CAST(created_on AS DATE) = CAST(GETDATE() AS DATE)
        AND value1 = 0
) AS time_intervals 
    ON t.mach_id = time_intervals.mach_id
    -- 区间判断：如果end_time不为空，取[start_time, end_time)；为空则删除start_time之后的所有记录（可按需调整）
    AND t.created_on >= time_intervals.start_time
    AND (t.created_on < time_intervals.end_time OR time_intervals.end_time IS NULL);

方式二：EXISTS子查询（适合大表，性能更优）

如果目标表数据量很大，用EXISTS子查询可能更高效（尤其是TargetTable在mach_id和created_on上有索引时）：

DELETE FROM TargetTable t
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT 
            mach_id,
            created_on AS start_time,
            lead(created_on) OVER (PARTITION BY mach_id ORDER BY created_on) AS end_time
        FROM MyTable 
        WHERE 
            field_name = 'someValue' 
            AND CAST(created_on AS DATE) = CAST(GETDATE() AS DATE)
            AND value1 = 0
    ) AS time_intervals
    WHERE t.mach_id = time_intervals.mach_id
        AND t.created_on >= time_intervals.start_time
        AND (t.created_on < time_intervals.end_time OR time_intervals.end_time IS NULL)
);

重要提醒 #

• 先测试再删除：建议先把DELETE改成SELECT *，确认要删除的记录完全符合预期，避免误删数据。比如：

  SELECT t.*
  FROM TargetTable t
  JOIN (...) AS time_intervals 
      ON t.mach_id = time_intervals.mach_id
      AND t.created_on >= time_intervals.start_time
      AND (t.created_on < time_intervals.end_time OR time_intervals.end_time IS NULL);
  

• 时间精度处理：如果created_on是带时分秒的datetime类型，注意区间比较的精度，避免漏掉或多删记录。
• 索引优化：如果两张表数据量较大，建议在mach_id、created_on字段上创建索引，提升查询和删除的性能。

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