SQL中如何处理Email列重复asd片段并展示去重后的数据

原SQL用于统计重复邮箱的记录数：

SELECT email, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

现在需要实现：检查Email列内容，移除其中重复出现的"asd"片段（仅保留第一个，若该片段只出现一次则不处理），并输出处理后的完整表数据。

当前表数据 #

users表现有数据：

ID   NAME   EMAIL
----------------------------
1    John   asd@asd@msp@bw
2    Sam    asd@com@wap
3    Tom    asd@wap@mop@asd
4    Bob    asd@sap@bad@asd
5    Tom    asd@man@asd@can

期望输出结果 #

处理后预期的表数据：

ID   NAME   EMAIL
------------------------
1    John   asd@msp@bw
2    Sam    asd@com@wap
3    Tom    asd@wap@mop
4    Bob    asd@sap@bad
5    Tom    asd@man@can

不同数据库的实现方案 #

MySQL（8.0+ 支持递归CTE） #

通过递归拆分邮箱片段，过滤掉重复的"asd"后重新拼接：

WITH RECURSIVE split_email AS (
    SELECT 
        ID,
        NAME,
        EMAIL,
        1 AS pos,
        SUBSTRING_INDEX(SUBSTRING_INDEX(EMAIL, '@', 1), '@', -1) AS part,
        SUBSTRING(EMAIL, LENGTH(SUBSTRING_INDEX(EMAIL, '@', 1)) + 2) AS remaining
    FROM users
    UNION ALL
    SELECT 
        ID,
        NAME,
        EMAIL,
        pos + 1,
        -- 若当前片段是asd且之前已经出现过，则标记为NULL后续过滤
        CASE WHEN SUBSTRING_INDEX(remaining, '@', 1) = 'asd' 
             AND EXISTS (SELECT 1 FROM split_email se WHERE se.ID = split_email.ID AND se.part = 'asd')
             THEN NULL 
             ELSE SUBSTRING_INDEX(remaining, '@', 1) 
        END AS part,
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, '@', 1)) + 2) AS remaining
    FROM split_email
    WHERE remaining != ''
)
SELECT 
    ID,
    NAME,
    GROUP_CONCAT(part SEPARATOR '@') AS EMAIL
FROM split_email
WHERE part IS NOT NULL
GROUP BY ID, NAME
ORDER BY ID;

PostgreSQL #

利用数组拆分和聚合函数，保留第一个"asd"并去除后续重复项：

SELECT
    ID,
    NAME,
    (SELECT string_agg(part, '@' ORDER BY idx)
     FROM (
         SELECT part, idx,
                -- 标记asd的出现次数，只保留第一次
                CASE WHEN part = 'asd' THEN ROW_NUMBER() OVER(PARTITION BY part ORDER BY idx) ELSE 1 END AS rn
         FROM unnest(string_to_array(EMAIL, '@')) WITH ORDINALITY AS t(part, idx)
     ) AS filtered
     WHERE part != 'asd' OR rn = 1) AS EMAIL
FROM users
ORDER BY ID;

SQL Server（2016+ 支持STRING_SPLIT） #

拆分邮箱片段后，过滤掉重复的"asd"再拼接：

WITH split_email AS (
    SELECT
        ID,
        NAME,
        value AS part,
        -- 这里用ORDER BY (SELECT NULL)保证拆分顺序，SQL Server 2022+可直接用STRING_SPLIT的ordinal参数
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT NULL)) AS idx
    FROM users
    CROSS APPLY STRING_SPLIT(EMAIL, '@')
),
filtered_parts AS (
    SELECT
        ID,
        NAME,
        part,
        idx,
        CASE WHEN part = 'asd' THEN ROW_NUMBER() OVER(PARTITION BY ID, part ORDER BY idx) ELSE 1 END AS rn
    FROM split_email
)
SELECT
    ID,
    NAME,
    STRING_AGG(CASE WHEN part = 'asd' AND rn > 1 THEN NULL ELSE part END, '@') WITHIN GROUP (ORDER BY idx) AS EMAIL
FROM filtered_parts
GROUP BY ID, NAME
ORDER BY ID;

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