是否有一种方法可以在Server 2017中确定查询列中是否存在重复项并更改该输入？

Question

我正在编写SQL Server 2017的查询，该查询确定员工的电子邮件，如下所示：

firstname.lastname123@email.com。

这三个数字是他们的员工ID的一部分。(我没有选择这种方式来确定电子邮件，也无法改变它)。

我遇到了一个问题，就是碰巧有一个雇员的名字和最后三位数的雇员ID是一样的。当这种情况发生时，我需要在电子邮件中添加一个数字，以便使他们成为唯一的，即，

示例：

姓名: John，员工ID: 00123 john.doe123@email.com

姓名: John，员工ID: 12123 john.doe1231@email.com

姓名: John，员工ID: 98123 john.doe1232@email.com

我不需要假设会有超过10个相同的名字和号码，所以不应该有超过4位数的电子邮件。此外，它确定谁的电子邮件中的数字较小的方式是根据员工ID的顺序。员工ID越低的人就有前面描述的顺序中的早期电子邮件。

创建他们电子邮件的查询只是简单地提取他们的ID的名字、姓氏和最后三位数字，并在一个列中将它们连接在一起。(第一+ '.‘’+最后+右(em_id，3)+ 'email.com')

如果存在重复项，如何/如何实现才能创建上述所需的结果？

Answer 1

您可以使用row_number()和cast函数将其转换为varchar，如下所示：

select (name + '.' 
        + surname 
        + right(Employee_ID,3) 
        + cast(row_number() over(partition by name, surname order by Employee_ID) as varchar) 
        + 'email.com') 
from employee

这样你就会得到如下的结果：

• john.doe1231@email.com
• john.doe1232@email.com
• john.doe1233@email.com
• peter.peterson2441@email.com

经过这一修改：

select case when row_number() over(partition by name, surname order by Employee_ID) > 1 
       then (name + '.' 
        + surname 
        + right(Employee_ID,3) 
        + cast(row_number() over(partition by name, surname order by Employee_ID)-1 as varchar) 
        + 'email.com')
       else (name + '.' 
        + surname 
        + right(Employee_ID,3)
        + 'email.com')
       end
from employee    

你会得到你想要的结果。

这是一个演示

Answer 2

聚会晚了，但你真正需要的是已经存在的“匹配”电子邮件的计数。

DECLARE @emails TABLE (email VARCHAR(100));
INSERT @emails (email)
VALUES ('john.doe123@email.com'),('john.doe1231@email.com'),('john.doe1232@email.com');

DECLARE 
  @newbie VARCHAR(50) = 'john.doe123',
  @addy VARCHAR(50);

SELECT @addy = 
  CONCAT(@newbie,
         CASE WHEN CAST(COUNT(*) AS CHAR(1)) = '0' THEN '' ELSE CAST(COUNT(*) AS CHAR(1)) END)
FROM @emails
WHERE email LIKE CONCAT('%',@newbie,'%')

SELECT @addy;

如果没有匹配项(这是工作中的CASE表达式)，则返回没有后缀的电子邮件地址，或者如果有匹配项，则添加当前计数。由于系统是基于0的，所以不需要对COUNT结果进行操作。