与另一个表格中找到匹配的人（MS SQL Server）

Question

我有两个表：

“ person”表

ID          FirstName  LastName
----------- ---------- ----------
1           Janez      Novak
2           Matija     Špacapan
3           Francka    Joras

“用户列表”

ID    FullName
----- --------------------
1     Andrej Novak
2     Novak Peter Janez
3     Jana Novak
4     Andrej Kosir
5     Jan Balon
6     Francka Joras
7     France Joras

结果 ，查询必须从两个表中返回这些ID，即表Person中的firstName和lastname来自表“ table用户列表中”。名称和姓氏必须完全相同。 表用户列表中的fullname可以包括中间名 - 应该“忽略”。

比赛：Janez Novak = Janez Novak或Novak Janez或Janez Peter Novak

不是比赛：Janez Novak＆lt;＆gt; Janeza Novak或Jjanez Novak

想要结果：

ID   FirstName  LastName  ID   WholeName
---- ---------- --------- ---- -------------------
1    Janez      Novak     2    Novak Peter Janez
3    Francka    Joras     6    Francka Joras

这是我的查询：

SELECT 
    A.ID
    ,A.FirstName
    ,A.LastName
    ,B.ID
    ,B.WholeName
FROM    
    dbo.UserList B
    cross join dbo.Person A 
WHERE   
    (                                                
    CHARINDEX('"'+A.FirstName+'"', '"'+Replace(B.WholeName,' ','"')+'"') > 0
     AND CHARINDEX('"'+A.LastName+'"', '"'+Replace(B.WholeName,' ','"')+'"') > 0 
    )

当桌子中没有很多记录时，查询可以正常运行。

但是我的桌子有：“人” - ＆gt; 400K和“用户列表” - ＆gt; 14K记录。

我找到解决方案的方法是否可以，还是还有其他更有效的方法可以做到这一点？ 谢谢。

br

Answer 1

创建表

create table persons (
  id int IDENTITY(1,1) PRIMARY KEY,
  FirstName nvarchar(32) NOT NULL,
  LastName nvarchar(32) NOT NULL
);

create table users (
  id int IDENTITY(1,1) PRIMARY KEY,
  FullName nvarchar(32) NOT NULL
);

示例数据

INSERT INTO persons (FirstName, LastName)
values
('Janez','Novak'),
('Matija','Špacapan'),
('Francka','Joras');

INSERT INTO users (FullName)
VALUES
('Andrej Novak'),
('Novak Peter Janez'),
('Jana Novak'),
('Andrej Kosir'),
('Jan Balon'),
('Francka Joras'),
('France Joras'),

/* --EDIT: added sample data for wildcard testing-- */
('Franckas Joras'), -- added 's' after firstname
('Francka AJoras'), -- added 'A' before lastname
('Franckas AJoras'), -- both above
('Francka Jr. Joras'), -- added just midname
('Franckas Jr. Joras'); -- added 's' before firstname & added midname as well

查询（匹配名称）

SELECT p.id, p.FirstName, p.LastName, u.id as user_id, u.FullName
FROM persons p, users u
WHERE
  -- EDIT
  /* changed wildcards (added spaces on both sides)
  + added 2 more conditions without wildcards */
  u.FullName LIKE CONCAT(p.FirstName, ' % ', p.LastName)
  OR
  u.FullName LIKE CONCAT(p.LastName, ' % ', p.FirstName)
  OR
  u.FullName LIKE CONCAT(p.FirstName, ' ', p.LastName)
  OR
  u.FullName LIKE CONCAT(p.LastName, ' ', p.FirstName)

输出

​输出新样本数据（用于通配符测试）

跑步示例 sql fiddle> sql fiddle

上面的示例链接链接是mysql＆amp;该代码在SQL Server上正常工作

Answer 2

您的架构被打破了：P

有各种启发性进行匹配，但我希望您能找到反例破坏您尝试的任何方法。例如，这四个人呢：彼得·史密斯，皮特·史密斯，彼得·史密森和皮特·史密斯？

这是一个％喜欢％方法，我希望它会很慢。

SELECT p.ID, p.FirstName, p.LastName, u.ID, u.FullName,
    CASE WHEN COUNT(*) OVER (PARTITION BY p.ID) > 1 THEN 0 ELSE 1 END AS MatchIsUnique
FROM Person p
    INNER JOIN UserList u
        ON u.FullName LIKE p.FirstName + '%'
        AND u.LastName LIKE '%' + p.LastName

这是基于空间字符是定界符的假设的字符串操纵方法。

SELECT p.ID, p.FirstName, p.LastName, u.ID, u.FullName,
    CASE WHEN COUNT(*) OVER (PARTITION BY p.ID) > 1 THEN 0 ELSE 1 END AS MatchIsUnique
FROM Person p
    INNER JOIN UserList u
        ON p.FirstName = SUBSTRING(@FullName, 0, CHARINDEX(' ', @Fullname))
        AND p.LastName = SUBSTRING(@FullName, LEN(@FullName) - CHARINDEX(' ', REVERSE(@Fullname))+1, CHARINDEX(' ', REVERSE(@Fullname)))

可能也很慢。也许您可以通过添加

• substring（@fullname，0，CharIndex（''，@fullname））和
• substring（@fullname，len（@fullname）-Charindex（''''''' ，反向（@fullname））+1，charindex（''，reverse（@fullname）））

作为计算列并索引它们。

Answer 3

您可以尝试尝试的一种方法是将全名分为行，然后进行比较，仅选择名字和姓氏匹配的方法：

select Max(m.id) Id, max(m.firstname) FirstName, Max(m.lastname) LastName, 
  u.id, Max(u.fullname) FullName
from userlist u
cross apply String_Split(fullname,' ')
cross apply (
    select *
    from person p
    where p.firstname = value or p.lastname = value
)m
group by u.id 
having Count(*)=2;

<

a href =“ https：//i.sstatic.net/bhijo.png” =“ nofollow noreferrer”>