sql - 在 SQL 中以不同的顺序查找姓名和姓氏

Question

我正在尝试编写一个 SQL 查询，该查询能够以不同的方式找到“暴露”的相同值。
现在我尝试更好地解释。

我有一列包含姓名和姓氏(如果您有多个)，所有内容都像这样:

--------------------------------------
|              TABLE_1               |
--------------------------------------
|                NAME                |
--------------------------------------
|           John Frusciante          |
--------------------------------------                   
|            Gilmour David           |
--------------------------------------
|            Sinatra Frank           |
--------------------------------------
|             David Bowie            |
--------------------------------------
|           Frusciante John          |
--------------------------------------
|     Wilhelm Friedrich Nietzsche    |
--------------------------------------

问题是名字和姓氏并不总是按顺序排列的。

我怎样才能进行类似的查询

SELECT * FROM TABLE_1 WHERE NAME='JOHN FRUSCIANTE'

并找到 2 个结果？

Answer 1

这是一种方法-忽略我在对您的问题的评论中提到的大部分细微之处。我提到的唯一一个是不区分大小写的搜索。

输入，例如“John Frusciante”，作为绑定(bind)变量给出，:i_name .名称可以是一个、两个、三个或任何其他数量的“ token ”——它们可以以任何顺序出现，包括像 Hussein Obama Barack 这样无意义的顺序。 (其中奥巴马是姓氏，巴拉克侯赛因是给定的名字；在美国术语中，名字和中间名)。对于测试，我使用“John Frusciante”作为绑定(bind)变量。

正则表达式很方便，但并不快。可以通过各种方式加快查询速度(使用标准字符串函数，但在 Oracle 12.1 或更高版本中也可以使用 lateral 或 cross apply 子句等)。其中一个问题是 listagg()。如果您的 Oracle 数据库版本为 11.1 或更低，因为此功能仅在 11.2 中引入。

该策略很简单 - 将每个名称分解为其标记，然后按字母顺序再次将它们聚合回来。我假设该表有 id列(如果不是，并且如果数据在存储表中，我可以使用 rowid ，否则我可以在附加步骤中即时创建 id)。

with
  table_1 (id, name) as (
    select 1, 'John Frusciante'             from dual union all
    select 2, 'Gilmour David'               from dual union all  
    select 3, 'Sinatra Frank'               from dual union all
    select 4, 'David Bowie'                 from dual union all
    select 5, 'Frusciante John'             from dual union all
    select 6, 'Wilhelm Friedrich Nietzsche' from dual
  )
, prep (id, name, ordered_name) as (
    select  id, name, 
            listagg(regexp_substr(name,'\S+', 1, level), ' ') 
              within group
                (order by regexp_substr(name,'\S+', 1, level))
    from    table_1
    connect by  level <= regexp_count(name, '\S+')
            and prior id = id
            and prior sys_guid() is not null
    group   by id, name
  )
select name
from   prep
where  lower(ordered_name) = 
         (select  lower(listagg(regexp_substr(:i_name,'\S+', 1, level), ' ') 
                  within group 
                    (order by regexp_substr(:i_name,'\S+', 1, level)))
          from    dual
          connect by level <= regexp_count(:i_name, '\S+')
         )
;

输出(用于输入 'John Frusciante'):

NAME
---------------
John Frusciante
Frusciante John