如何使用SQL创建员工邮箱地址并处理重名问题

你最初的基础邮箱生成查询如下：

SELECT first_name || last_name || '@mycompany.com' AS employee_email
FROM employee

该查询在无重名时可以正常返回结果，示例输出：

"JaredEly@mycompany.com"
"MarySmith@mycompany.com"
"PatriciaJohnson@mycompany.com"
"LindaWilliams@mycompany.com"
"BarbaraJones@mycompany.com"

但无法处理重名场景，按照需求：同名员工中第一个分配无数字后缀的基础邮箱，从第二个开始在姓名段后追加2、3……的递增序号，可通过窗口函数实现该逻辑。

实现代码 #

核心思路是通过ROW_NUMBER()窗口函数按姓名组合分组，给同姓名的员工按指定规则排序编号，再根据编号判断是否追加数字后缀：

WITH name_ranked_employee AS (
    SELECT
        first_name,
        last_name,
        ROW_NUMBER() OVER (
            PARTITION BY first_name, last_name
            -- 可根据业务规则调整排序字段，比如按员工ID、入职时间排序确定邮箱分配优先级
            ORDER BY employee_id
        ) AS same_name_seq
    FROM employee
)
SELECT
    first_name || last_name
    || CASE WHEN same_name_seq = 1 THEN '' ELSE CAST(same_name_seq AS VARCHAR) END
    || '@mycompany.com' AS employee_email
FROM name_ranked_employee

针对两位John Smith的测试场景，该查询会返回：

"JohnSmith@mycompany.com"
"JohnSmith2@mycompany.com"

后续新增同名员工会自动追加3、4等连续序号，符合规则要求。

注意事项 #

• 排序字段可根据实际业务调整：如果需要按入职先后分配无后缀邮箱，将排序条件替换为ORDER BY hire_date ASC即可
• 不同数据库语法略有差异：MySQL环境下可将||拼接替换为CONCAT()函数，PostgreSQL、Oracle可直接使用上述||拼接语法，SQL Server环境下可根据版本适配对应类型转换、拼接写法。

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