合并productinstance表重复IMEI记录后删除重复项的实现方法

当然可行！这种数据合并+去重的需求在日常数据库维护里太常见了，我给你分步骤拆解一下操作流程，以MySQL为例（如果是PostgreSQL、SQL Server等其他数据库，核心逻辑一致，语法稍作调整即可）：

1. 先备份数据（重中之重！） #

不管操作多熟练，先备份表数据永远是第一要务，避免操作失误导致数据丢失：

CREATE TABLE productinstance_backup AS SELECT * FROM productinstance;

2. 将重复记录的invoiceNo和invoicedate合并到原记录 #

根据你的描述，每个重复组里有两条记录：

• 原记录：serialNr不为空，但invoiceNo和invoicedate为NULL
• 重复记录：serialNr为NULL，但invoiceNo和invoicedate有有效值

我们可以通过分组查询拿到每个IMEI对应的有效发票信息，再更新到原记录中：

UPDATE productinstance pi
JOIN (
    -- 分组获取每个IMEI的有效发票信息（因为每个组只有一条有值，MAX会自动取非NULL的那个）
    SELECT imei, MAX(invoiceNo) AS invoiceNo, MAX(invoicedate) AS invoicedate
    FROM productinstance
    GROUP BY imei
    HAVING COUNT(*) > 1 -- 只处理存在重复的IMEI
) pi_dup ON pi.imei = pi_dup.imei
-- 只更新原记录（也就是发票字段为空的那条）
SET pi.invoiceNo = pi_dup.invoiceNo,
    pi.invoicedate = pi_dup.invoicedate
WHERE pi.invoiceNo IS NULL AND pi.invoicedate IS NULL;

3. 删除重复的记录 #

现在原记录已经补全了发票信息，我们需要删除那些serialNr为NULL的重复记录，这里提供两种常用方法：

方法一：关联删除（适合所有MySQL版本） #

直接删除存在同IMEI且serialNr非空记录的空serialNr行：

DELETE pi1
FROM productinstance pi1
JOIN productinstance pi2 ON pi1.imei = pi2.imei
WHERE pi1.serialNr IS NULL 
  AND pi2.serialNr IS NOT NULL;

方法二：窗口函数删除（适合MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

给每个IMEI的记录排序，优先保留serialNr非空的记录，删除排序靠后的重复项：

WITH ranked_records AS (
    SELECT *,
           -- 给每个IMEI的记录排序：serialNr非空的排第1位，空的排第2位
           ROW_NUMBER() OVER (PARTITION BY imei ORDER BY CASE WHEN serialNr IS NOT NULL THEN 1 ELSE 2 END) AS rn
    FROM productinstance
)
DELETE FROM productinstance
WHERE id IN (SELECT id FROM ranked_records WHERE rn > 1);

4. 验证操作结果 #

最后执行查询确认没有重复记录，且所有原记录都已补全发票信息：

-- 检查是否还有重复的IMEI
SELECT imei, COUNT(*) AS record_count
FROM productinstance
GROUP BY imei
HAVING COUNT(*) > 1;

-- 检查原记录的发票字段是否已填充
SELECT id, imei, invoiceNo, invoicedate, serialNr
FROM productinstance
WHERE invoiceNo IS NULL AND invoicedate IS NULL;

如果这两个查询都返回空结果，说明操作成功！

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