Oracle SQL按自定义规则实现姓名去重的分组排序方法

核心思路 #

要实现你需要的去重逻辑，核心分为两步：

• 对full_name字段做标准化处理，消除空格差异、单词排序差异，让逻辑上属于同一人的姓名生成完全一致的唯一标识
• 按标准化后的唯一标识分组，每组取id最小的记录，即可得到你需要的id=1、id=3的两条结果

标准化处理规则 #

针对你的需求，我们采用如下规则生成姓名唯一标识：

1. 把full_name首尾的空格去除，按中间的空格拆分为独立单词（如果没有空格则整个姓名为一个单词）
2. 将拆分得到的单词列表按字典序排序，消除单词前后顺序的差异
3. 将排序后的单词直接拼接为无空格的字符串，作为分组的唯一标识
   按照上述规则：

• John Smith和Smith John生成的标识都是JohnSmith
• Jim Jonson和JimJonson生成的标识都是JimJonson

不同数据库实现示例 #

MySQL 8.0+ 版本实现 #

WITH normalized_names AS (
    SELECT 
        id,
        full_name,
        -- 生成标准化的姓名唯一标识
        (SELECT GROUP_CONCAT(word ORDER BY word SEPARATOR '') 
         FROM JSON_TABLE(
             CONCAT('["', REPLACE(TRIM(full_name), ' ', '","'), '"]'),
             '$[*]' COLUMNS(word VARCHAR(255) PATH '$')
         ) AS words) AS name_key
    FROM 你的表名
)
SELECT id, full_name 
FROM (
    SELECT 
        id, 
        full_name,
        -- 同标识分组内按id升序排序，取第一条就是最小id的记录
        ROW_NUMBER() OVER (PARTITION BY name_key ORDER BY id ASC) AS rn
    FROM normalized_names
) t
WHERE rn = 1;

如果你的姓名固定最多为2个单词，也可以用更简单的兼容低版本MySQL的写法：

SELECT MIN(id) AS id, ANY_VALUE(full_name) AS full_name
FROM 你的表名
GROUP BY 
    CASE 
        WHEN INSTR(full_name, ' ') = 0 THEN REPLACE(full_name, ' ', '')
        ELSE 
            CONCAT(
                LEAST(SUBSTRING_INDEX(full_name, ' ', 1), SUBSTRING_INDEX(full_name, ' ', -1)),
                GREATEST(SUBSTRING_INDEX(full_name, ' ', 1), SUBSTRING_INDEX(full_name, ' ', -1))
            )
    END
ORDER BY id ASC;

PostgreSQL 版本实现 #

WITH normalized_names AS (
    SELECT 
        id,
        full_name,
        string_agg(word, '' ORDER BY word) AS name_key
    FROM 你的表名,
         unnest(string_to_array(TRIM(full_name), ' ')) AS word
    GROUP BY id, full_name
)
SELECT id, full_name
FROM (
    SELECT 
        id,
        full_name,
        ROW_NUMBER() OVER (PARTITION BY name_key ORDER BY id) AS rn
    FROM normalized_names
) t
WHERE rn = 1;

提示：请将上述代码中的你的表名替换为你实际使用的表名称即可运行。

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