药品销售数据库去重需求：保留有效记录及历史删除记录

需求背景 #

我有一个包含约58.8万条记录的药品销售数据库，药店注册状态分为**Valid（有效）和Deleted（已删除）**两类。由于地址、邮编变更等原因，同一Customer Code（客户编码）可能同时存在两种状态：

示例数据1：同一客户编码同时存在两种状态 #

示例数据2：仅存在Deleted状态的历史药店 #

清洗规则 #

• 若同一Customer Code同时存在Valid和Deleted状态，仅保留Valid记录
• 若同一Customer Code仅存在Deleted状态，保留该Deleted记录

期望结果 #

解决方案：SQL查询语句 #

方法1：使用窗口函数（推荐，性能更优） #

适用于支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH customer_state AS (
    SELECT 
        name,
        `Register State`,
        `Customer Code`,
        -- 按客户编码分组，给Valid状态优先排序
        ROW_NUMBER() OVER (
            PARTITION BY `Customer Code` 
            ORDER BY CASE `Register State` WHEN 'Valid' THEN 1 ELSE 2 END
        ) AS rn
    FROM your_table_name
)
SELECT name, `Register State`, `Customer Code`
FROM customer_state
WHERE rn = 1;

说明：窗口函数ROW_NUMBER()会给每个Customer Code下的记录排序，Valid状态排第一，取每条分组的第一条即可满足需求。如果同一客户编码下有多个Valid记录，会保留第一条；若需要去重Valid记录，可以把ROW_NUMBER()换成DENSE_RANK()并调整逻辑。

方法2：使用子查询 #

兼容低版本数据库（如MySQL 5.x）：

-- 先筛选出所有存在Valid状态的客户编码的Valid记录
SELECT name, `Register State`, `Customer Code`
FROM your_table_name
WHERE `Register State` = 'Valid'

UNION ALL

-- 再筛选出仅存在Deleted状态的客户编码的记录
SELECT name, `Register State`, `Customer Code`
FROM your_table_name t1
WHERE `Register State` = 'Deleted'
AND NOT EXISTS (
    SELECT 1 
    FROM your_table_name t2 
    WHERE t2.`Customer Code` = t1.`Customer Code` 
    AND t2.`Register State` = 'Valid'
);

说明：通过UNION ALL合并两部分结果：第一部分是所有Valid记录，第二部分是那些没有对应Valid记录的Deleted记录。

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