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

Tadej 发表于 Dev

撒迪厄斯

我有两张桌子：

表“人”

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 存在于表 UserList 中。姓名和姓氏必须完全相同。表 UserList 中的 FullName 可以包含中间名 - 应该“忽略”。

比赛：Janez Novak = Janez Novak OR Novak Janez OR Janez Peter Novak

不匹配：Janez Novak <> 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 
    )

当表中的记录不多时，查询工作正常。

但是我的表有：“Person”-> 400k 和“UserList”-> 14k 记录。

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

故事

创建表

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)

输出