oracle - 在 Oracle 中创建唯一的名字和姓氏字符串

Question

我可以通过编程方式执行此操作，但一直在寻找更简洁的解决方案。

假设我有下表:

First Name      Last Name
Smith           Albert       
Smith           Alphonse    
Smith           Jason         
Johnson         Charles
Roberts         Chris
Roberts         Christian

我想创建一个具有以下规则的 unique

• 如果姓氏已经是唯一的，则只返回姓氏
• 如果相同的姓氏返回第一个首字母(或更多)后跟句点然后是姓氏

对于 Albert Smith，我会返回 Alb.Smith
对于 Charles Johnson，我会返回 Johnson
对于 Christion Roberts，我会返回 Christ.Roberts

有没有人对如何直接在 Oracle SQL 语句中完成此操作有任何想法，还是我应该坚持在程序中完成此操作？

Answer 1

版本 recursive subquery refactoring (CTE)，需要 11gR2:

with t (last_name, first_name, orig_rn, part, part_length, remaining) as (
  select last_name, first_name,
    row_number() over (order by last_name, first_name),
    cast (null as varchar2(20)), 0, length(first_name)
  from t42
  union all
  select last_name, first_name, orig_rn,
    part || substr(first_name, part_length + 1, 1),
    part_length + 1,
    remaining - 1
  from t
  where remaining > 0
),
u as (
  select last_name, first_name, orig_rn, part, part_length,
    count(distinct orig_rn) over (partition by last_name) as last_name_count,
    count(distinct orig_rn) over (partition by last_name, part) as part_count
  from t
),
v as (
  select last_name, first_name, orig_rn, part, last_name_count,
  row_number() over (partition by orig_rn order by part_length) as rn
  from u
  where (part_count = 1 or part = first_name)
)
select case when last_name_count = 1 then null
  when part = first_name then first_name || ' '
  else part || '. '
  end || last_name as condendsed_name
from v
where rn = 1
order by orig_rn;

给出:

CONDENSED_NAME                               
----------------------------------------------
Johnson                                        
Chris Roberts                                  
Christ. Roberts                                
Alb. Smith                                     
Alp. Smith                                     
J. Smith                                       

SQL Fiddle .

t CTE 是递归的。它从原始表格行开始，并为每个可能的名字缩写生成额外的行:

with t (last_name, first_name, orig_rn, part, part_length, remaining) as (
  select last_name, first_name,
    row_number () over (order by last_name, first_name),
    cast (null as varchar2(20)), 0, length(first_name)
  from t42
  union all
  select last_name, first_name, orig_rn,
    part || substr(first_name, part_length + 1, 1),
    part_length + 1,
    remaining - 1
  from t
  where remaining > 0
)
select last_name, first_name, part
from t
where last_name = 'Johnson'
order by orig_rn, part_length;

LAST_NAME            FIRST_NAME           PART                   
-------------------- -------------------- ------------------------
Johnson              Charles                                       
Johnson              Charles              C                        
Johnson              Charles              Ch                       
Johnson              Charles              Cha                      
Johnson              Charles              Char                     
Johnson              Charles              Charl                    
Johnson              Charles              Charle                   
Johnson              Charles              Charles                  

下一个 CTE，u(是的，对名字感到抱歉，我没有灵感)比较所有行的值并计算出现次数。计数为 1 的任何事物都是唯一的。

...
u as (
  select last_name, first_name, orig_rn, part, part_length,
    count(distinct orig_rn) over (partition by last_name) as last_name_count,
    count(distinct orig_rn) over (partition by last_name, part) as part_count
  from t
)
select last_name, first_name, part, last_name_count, part_count
from u
where last_name = 'Roberts'
order by orig_rn, part_length;

LAST_NAME            FIRST_NAME           PART                     LAST_NAME_COUNT PART_COUNT
-------------------- -------------------- ------------------------ --------------- ----------
Roberts              Chris                                                       2          2 
Roberts              Chris                C                                      2          2 
Roberts              Chris                Ch                                     2          2 
Roberts              Chris                Chr                                    2          2 
Roberts              Chris                Chri                                   2          2 
Roberts              Chris                Chris                                  2          2 
Roberts              Christian                                                   2          2 
Roberts              Christian            C                                      2          2 
Roberts              Christian            Ch                                     2          2 
Roberts              Christian            Chr                                    2          2 
Roberts              Christian            Chri                                   2          2 
Roberts              Christian            Chris                                  2          2 
Roberts              Christian            Christ                                 2          1 
Roberts              Christian            Christi                                2          1 
Roberts              Christian            Christia                               2          1 
Roberts              Christian            Christian                              2          1 

第三个CTEv只看唯一的，然后根据唯一值的长度进行排序；因此，在所有记录中唯一的记录的名字的最短缩写排名为 1。

...
v as (
  select last_name, first_name, orig_rn, part, last_name_count,
  row_number() over (partition by orig_rn order by part_length) as rn
  from u
  where (part_count = 1 or part = first_name)
)
select last_name, first_name, part, last_name_count
from v
where rn = 1
order by orig_rn;

LAST_NAME            FIRST_NAME           PART                     LAST_NAME_COUNT
-------------------- -------------------- ------------------------ ---------------
Johnson              Charles                                                     1 
Roberts              Chris                Chris                                  2 
Roberts              Christian            Christ                                 2 
Smith                Albert               Alb                                    3 
Smith                Alphonse             Alp                                    3 
Smith                Jason                J                                      3 

然后最终查询只提取那些排名 1 的，它们是最短的唯一值，并按照您想要的方式格式化它们。

如果两个人的名字完全相同，那么两个人的名字都会全拼 (demo)，这似乎正是您希望从评论中看到的。

不确定这是否真的符合“更清洁”的条件，除了它只命中原始表一次。