时间戳

首页 > 解决方案 > 在 SQL 中以不同的顺序查找姓名和姓氏

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

问题描述

我正在尝试编写一个 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 个结果？

标签： sqloracle

解决方案

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

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

正则表达式很方便，但并不快。可以通过各种方式使查询更快（使用标准字符串函数，但也可以在 Oracle 12.1 或更高版本中使用lateralorcross 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

推荐阅读