mysql - SQL - 匹配两个表中@符号之前的电子邮件地址文本

Question

我正在尝试从两个表中匹配相似的电子邮件地址。我想要做的是查找表 1 中的电子邮件地址与表 2 中的电子邮件地址相似的记录(@ 符号前的文本相同)。例如，JohnSmith@gmail.com 将匹配 JohnSmith@yahoo.com。

Answer 1

对于 Access，您可以使用这样的查询:

SELECT
    t1.ID AS ID1,
    t1.Email AS Email1,
    t2.ID AS ID2,
    t2.Email AS Email2
FROM
    Table1 AS t1
    INNER JOIN
    Table2 as t2
        ON Left(t1.Email,InStr(t1.Email,'@')-1) = Left(t2.Email,InStr(t2.Email,'@')-1)

编辑回复:评论

如果由于您尝试加入的字段中的空值而收到“条件表达式中的数据类型不匹配”错误，请尝试此操作

SELECT 
    t1.ID AS ID1, 
    t1.Username AS Email1, 
    t2.ID AS ID2, 
    t2.email AS Email2
FROM 
    USERS AS t1 
    INNER JOIN 
    LICENSE AS t2 
        ON Left(Nz(t1.Username,''),InStr(Nz(t1.Username,''),'@')-1) = Left(Nz(t2.email,''),InStr(Nz(t2.email,''),'@')-1);

编辑2

...或者您可能更喜欢这样的内容:

SELECT 
    t1.ID,
    t1.Username AS Email
FROM 
    USERS AS t1 
WHERE
    EXISTS
        (
            SELECT * 
            FROM LICENSE AS t2 
                WHERE Left(Nz(t1.Username,''),InStr(Nz(t1.Username,''),'@')-1) = Left(Nz(t2.email,''),InStr(Nz(t2.email,''),'@')-1)
        )