SQL CASE语句检查字段中的多个拼写并在找到时返回相同的值？

Question

我正在创建一个 Oracle 数据库转换脚本，以将记录从一个旧的单一表移动到一个具有更多字段以及子/引用表的新表，以实现更好的完整性。当我在 Table1 中为 VENDOR 运行 SELECT DISTINCT 时，我得到了 58 个结果。下面是一个例子，说明这些结果对于相同的含义有多么不同:

• WWT
• &nbspWWT
• 全局技术
•  全局技术
• 环球科技
• 全局技术

在我的引用表中，我已经为这个特定值设置了一条记录作为“World Wide Technology, Inc.”

有没有一种方法可以指定(也许是 CASE 语句？)当这些不同的拼写用于返回我在我的子表中具有的值时(例如)“World Wide Technology, Inc.” ?

此表是要处理的最简单的子表，因为它只有 17 个不同的值，我已插入其中，而 Table1 有 58 个不同的行返回。到目前为止，我有:

INSERT INTO Table2 (VendorID, col2, col3, col4, etc...)
SELECT T3.ID, T1.col7, T1.col8, T1.col9, etc...
FROM Table1 T1
INNER JOIN Table3 T3 ON LTRIM(UPPER(T1.Vendor)) = UPPER(T3.Vendor_Name)

我在表 1 中有 1349 条记录，但这(仅针对 SELECT 部分)仅返回 418 条记录。

有谁知道我在这里要做什么？

Answer 1

如果您没有也不想创建映射表，则案例方法类似于您之前关于日期的问题。要转换所有值，您需要执行以下操作:

select case
  when vendor in (
    'WWT',
    ' WWT',
    'Worldwide Technologies',
    ' Worldwide Technologies',
    ' WorldWide Technology',
    'World Wide Technology'
  ) then 'World Wide Technology, Inc.'
  when t1.vendor in (
    'ACME',
    ' acme'
  ) then 'ACME, Inc.'
  ... other groups of original values with their new equivalents
  else null
  end as vendor_name
from t1;

在您的情况下，您可能会硬编码新的 vendor_id 而不是名称，否则您只需返回 t3 即可获取 ID基于您的映射名称:

INSERT INTO Table2 (VendorID, col2, col3, col4, etc...)
SELECT case
  when t1.vendor in (
    'WWT',
    ' WWT',
    'Worldwide Technologies',
    ' Worldwide Technologies',
    ' WorldWide Technology',
    'World Wide Technology'
  ) then 42 -- ID for 'World Wide Technology, Inc.'
  when t1.vendor in (
    'ACME',
    ' acme'
  ) then 76 -- ID for 'ACME, Inc.'
  ... other groups of original values with their new equivalents
  else null
  end as vendor_id, T1.col7, T1.col8, T1.col9, etc...
FROM Table1 T1;

如果您有很多不同的值，这些值仅因大小写和前导/尾随空格而异，您可以减少要检查的值的数量，例如:

select case
  when trim(upper(t1.vendor)) in (
    'WWT',
    'WORLDWIDE TECHNOLOGIES',
    'WORLD WIDE TECHNOLOGY'
  ) then 42 -- ID for 'World Wide Technology, Inc.'
  when trim(upper(t1.vendor)) in (
    'ACME'
  ) then 76 -- ID for 'ACME, Inc.'
  else null
  end as vendor_name
from t1;

您还可以删除标点符号等。基本上，无论您首先使用什么查询表达式来识别不同的值，都必须与您在 case 表达式中使用的任何内容相匹配。所以在这个例子中，而不是你原来的 SELECT DISTINCT VENDOR FROM Table1 有 58 个值你会做 SELECT DISTINCT TRIM(UPPER(VENDOR)) FROM Table1 这将给出你更少，(稍微)减少了手动将每个匹配到新的供应商 ID 的痛苦。

如果你想使用 case 映射中的描述，你可以加入你的新查找表，然后在 join 子句中做 case:

select t1.vendor, t3.vendor_id, t3.description
from t1
left join t3 on t3.description = case
  when trim(upper(t1.vendor)) in (
    'WWT',
    'WORLDWIDE TECHNOLOGIES',
    'WORLD WIDE TECHNOLOGY'
  ) then 'World Wide Technology, Inc.'
  when trim(upper(t1.vendor)) in (
    'ACME'
  ) then 'ACME, Inc.'
  else null
  end;

VENDOR                    VENDOR_ID DESCRIPTION               
------------------------ ---------- ---------------------------
 Worldwide Technologies          42 World Wide Technology, Inc.
 World Wide Technology           42 World Wide Technology, Inc.
WWT                              42 World Wide Technology, Inc.
 AcMe                            76 ACME, Inc.                 

等显然，这只是一个演示。我已经将其设为左连接，因此如果您有一个尚未映射的值，或者在描述中有错字等，它将尝试插入一个空值。然后，您可以查找空值并根据需要填充它们，或者对新的(大概是外键)列进行非空约束，这样它就不会让您在没有匹配项的情况下插入 - 但这可能过于严格，再次取决于您的实际数据。