查询WWW_Login中LoginName为同名加1的重复邮箱账户

要同时满足邮箱重复、登录名成对（原名称+1）、创建时间间隔1分钟内这三个条件，可以通过自连接结合字符串匹配、时间差判断来实现，以下是适配需求的SQL语句（基于SQL Server）：

简洁适配版（严格匹配基础名和基础名+1规则） #

WITH LoginPairs AS (
    SELECT 
        *,
        -- 提取基础名称：若末尾是1则去掉，否则保留原名称
        CASE WHEN RIGHT(LoginName,1) = '1' THEN LEFT(LoginName, LEN(LoginName)-1) ELSE LoginName END AS BaseLoginName
    FROM WWW_Login WITH(NOLOCK)
    WHERE CreatedDate >= DATEADD(dd, -10, GETDATE())
)
SELECT 
    lp1.LoginName, lp1.EmailAddress, lp1.CreatedDate
FROM LoginPairs lp1
JOIN LoginPairs lp2
    ON lp1.EmailAddress = lp2.EmailAddress
    AND lp1.BaseLoginName = lp2.BaseLoginName
    AND lp1.LoginName != lp2.LoginName
    AND ABS(DATEDIFF(MINUTE, lp1.CreatedDate, lp2.CreatedDate)) <= 1
ORDER BY lp1.EmailAddress, lp1.CreatedDate;

严谨兼容版（适配含特殊字符的LoginName，如Post.Malone） #

如果你的LoginName可能包含点、下划线等特殊字符，且需要确保前缀完全一致（仅末尾多一个1），可以用更严谨的正则匹配逻辑：

SELECT 
    a.LoginName, a.EmailAddress, a.CreatedDate
FROM WWW_Login a WITH(NOLOCK)
JOIN WWW_Login b WITH(NOLOCK)
    ON a.EmailAddress = b.EmailAddress
    -- 匹配前缀一致，且其中一个末尾为1
    AND (
        (a.LoginName = b.LoginName + '1' OR b.LoginName = a.LoginName + '1')
        OR (
            PATINDEX('%[^0-9]%', REVERSE(a.LoginName)) > 0 
            AND PATINDEX('%[^0-9]%', REVERSE(b.LoginName)) > 0
            AND LEFT(a.LoginName, LEN(a.LoginName) - PATINDEX('%[^0-9]%', REVERSE(a.LoginName)) + 1) 
                = LEFT(b.LoginName, LEN(b.LoginName) - PATINDEX('%[^0-9]%', REVERSE(b.LoginName)) + 1)
            AND (RIGHT(a.LoginName,1) = '1' OR RIGHT(b.LoginName,1) = '1')
        )
    )
    -- 确保创建时间间隔≤1分钟
    AND ABS(DATEDIFF(MINUTE, a.CreatedDate, b.CreatedDate)) <= 1
WHERE 
    a.CreatedDate >= DATEADD(dd, -10, GETDATE())
    AND a.LoginName < b.LoginName
UNION ALL
SELECT 
    b.LoginName, b.EmailAddress, b.CreatedDate
FROM WWW_Login a WITH(NOLOCK)
JOIN WWW_Login b WITH(NOLOCK)
    ON a.EmailAddress = b.EmailAddress
    AND (
        (a.LoginName = b.LoginName + '1' OR b.LoginName = a.LoginName + '1')
        OR (
            PATINDEX('%[^0-9]%', REVERSE(a.LoginName)) > 0 
            AND PATINDEX('%[^0-9]%', REVERSE(b.LoginName)) > 0
            AND LEFT(a.LoginName, LEN(a.LoginName) - PATINDEX('%[^0-9]%', REVERSE(a.LoginName)) + 1) 
                = LEFT(b.LoginName, LEN(b.LoginName) - PATINDEX('%[^0-9]%', REVERSE(b.LoginName)) + 1)
            AND (RIGHT(a.LoginName,1) = '1' OR RIGHT(b.LoginName,1) = '1')
        )
    )
    AND ABS(DATEDIFF(MINUTE, a.CreatedDate, b.CreatedDate)) <= 1
WHERE 
    a.CreatedDate >= DATEADD(dd, -10, GETDATE())
    AND a.LoginName < b.LoginName
ORDER BY EmailAddress, CreatedDate;

关键逻辑说明 #

• 成对账户匹配：通过提取基础登录名或直接字符串拼接判断，确保两个账户属于同一前缀的名称和名称+1组合
• 时间间隔校验：用ABS(DATEDIFF(MINUTE, ...)) <=1保证两个账户的创建时间差不超过1分钟
• 10天数据过滤：保留了你原SQL中的时间范围条件
• 结果格式对齐：通过UNION ALL或自连接匹配，确保成对账户连续展示，和你给出的示例格式一致

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