编写MySQL语句保留手机号分配周期首尾记录并删除重复数据

需求明确：每周导入电话号码分配数据至MySQL表（含ph_index、ph_num、userid、last_update字段），需删除冗余记录，仅保留同一电话号码下，每个用户连续分配周期的最早与最晚记录（用户切换后视为新周期）。

核心思路 #

要实现这个需求，关键是先识别出同一号码下的用户连续分配周期——即当某号码的分配用户发生变化时，标记为新的周期。然后针对每个周期，筛选出首尾记录，删除其余中间记录。

适用MySQL 8.0+的高效方案（推荐） #

利用窗口函数LAG()识别周期切换，再通过分组筛选保留首尾记录，最后执行删除操作。

-- 1. 定义CTE，标记周期并筛选需保留的记录ID
WITH cycle_groups AS (
    SELECT 
        ph_index,
        ph_num,
        userid,
        last_update,
        -- 生成周期ID：当前记录与上一条用户不同时，开启新周期
        SUM(CASE WHEN prev_user != userid OR prev_user IS NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY ph_num ORDER BY last_update, ph_index
        ) AS cycle_id
    FROM (
        SELECT 
            ph_index,
            ph_num,
            userid,
            last_update,
            -- 获取当前号码的上一条记录用户
            LAG(userid) OVER (
                PARTITION BY ph_num ORDER BY last_update, ph_index
            ) AS prev_user
        FROM phone_assignments -- 替换为你的实际表名
    ) AS lagged_data
),
keep_ids AS (
    SELECT ph_index
    FROM cycle_groups
    WHERE (last_update, ph_index) IN (
        -- 取每个周期的最早记录（时间相同则取最小ID）
        SELECT MIN(last_update), MIN(ph_index)
        FROM cycle_groups
        GROUP BY ph_num, userid, cycle_id
        UNION ALL
        -- 取每个周期的最晚记录（时间相同则取最大ID）
        SELECT MAX(last_update), MAX(ph_index)
        FROM cycle_groups
        GROUP BY ph_num, userid, cycle_id
    )
)
-- 2. 删除冗余记录（执行前务必先备份或验证）
DELETE FROM phone_assignments
WHERE ph_index NOT IN (SELECT ph_index FROM keep_ids);

逻辑解释 #

1. cycle_groups CTE：

   • 通过LAG()函数获取同一号码的上一条记录用户，对比当前用户，判断是否开启新周期。
   • 用SUM()窗口函数累加周期标记，生成唯一的cycle_id——同一号码下连续分配给同一用户的记录会被归为同一个周期。

2. keep_ids CTE：

   • 按ph_num、userid、cycle_id分组，提取每组的最早和最晚记录的ph_index，形成需保留的ID集合。

3. 删除操作：

   • 删除不在保留集合中的记录，完成冗余清理。

注意事项 #

• 替换SQL中的phone_assignments为你的实际表名。
• 确保ph_index是唯一标识（主键或唯一索引），last_update为合法的日期/时间类型。
• 执行删除前必须验证：先运行SELECT * FROM phone_assignments WHERE ph_index NOT IN (SELECT ph_index FROM keep_ids)，确认待删除的记录符合预期，同时建议备份数据。
• 2000+条数据量下，该方案性能完全达标，窗口函数处理这类分组逻辑效率极高。

示例验证（对应需求中的示例2） #

经过上述SQL处理后，原9条记录会保留首尾周期的6条记录：

• user1第一个周期：记录1（最早）、记录3（最晚）
• user2周期：记录4（最早）、记录6（最晚）
• user1第二个周期：记录7（最早）、记录9（最晚）

完全符合需求中的预期结果。

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