时间戳

首页 > 解决方案 > 未应用 SQL 字符串排除条件

sql - 未应用 SQL 字符串排除条件

问题描述

最近，我一直在尝试优化用于从 Oracle 数据库中包含的大量数据中排除某些字符串的代码。但是，我遇到了条件问题，因为我试图排除的那些字符串仍然出现。代码示例如下所示：

WITH
    DUNS AS (
        select '%PERSON' as DUNS from DUAL UNION ALL
        select 'PH0%' as DUNS from DUAL UNION ALL
        select 'PH1%' as DUNS from DUAL UNION ALL
        select 'PH2%' as DUNS from DUAL UNION ALL
        select 'PH3%' as DUNS from DUAL UNION ALL
        select 'PH4%' as DUNS from DUAL UNION ALL
        select 'PH5%' as DUNS from DUAL UNION ALL
        select 'PH6%' as DUNS from DUAL UNION ALL
        select 'PH7%' as DUNS from DUAL UNION ALL
        select 'PH8%' as DUNS from DUAL UNION ALL
        select 'PH9%' as DUNS from DUAL UNION ALL
        select 'PH-PERSON' as DUNS from DUAL UNION ALL
        select 'TRIALACCT' as DUNS from DUAL
        ),
    ORGTYPE AS(
        select 'Do Not Assign' as ORGTYPE from DUAL UNION ALL
        select 'HGBU_Store_DIM' as ORGTYPE from DUAL UNION ALL
        select 'HGBU%' as ORGTYPE from DUAL
        ),
    CDQ AS(
        select 
            REGISTRY_ID, 
            NVL(DUNS,0) DUNS, 
            NVL(ORG_TYPE,0) ORG_TYPE, 
            NVL(TECH_ORG_TYPE,0) TECH_ORG_TYPE, 
            NVL(APPS_ORG_TYPE,0) APPS_ORG_TYPE
        from gtm_cdq a
        where not exists (select 1 from gtm_clean_accounts b where a.REGISTRY_ID=b.crm_registry_id)
        )
select * 
from CDQ g, DUNS h, ORGTYPE i
where g.DUNS not like h.DUNS
AND (g.ORG_TYPE not like i.ORGTYPE AND g.TECH_ORG_TYPE not like i.ORGTYPE AND g.APPS_ORG_TYPE not like i.ORGTYPE)

此代码成功运行后，我查看结果表并看到：

REGISTRY_ID DUNS    ORG_TYPE    TECH_ORG_TYPE   APPS_ORG_TYPE   DUNS    ORGTYPE
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge     %PERSON   Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH0%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH1%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH2%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH3%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH4%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH5%    Do Not Assign

有什么我想念的吗？我认为这一切都来自我指定 Org_Type 列不包含 HGBU 类型字符串的最后一行（条件）。不知何故，它根本没有得到处理。有任何想法吗？

非常感谢！

标签： sqloracle

解决方案

WHERE 子句中的逻辑没有按照您的意愿或期望进行。这是一个更简单的例子：

WITH
    DUNS AS (
        select '%A' as DUNS from DUAL UNION ALL
        select 'B%' as DUNS from DUAL),
    CDQ AS(
    select 1 as registry_id, 'AA' as DUNS, 'a' as org_type,
        'a' as tech_org_type, 'a' as apps_org_type
        from dual
    )
select g.duns, h.duns
from cdq g, duns h
where g.duns not like h.duns;

输出：

g.duns h.duns
AA     B%

带有 where 子句的连接将返回（笛卡尔）连接中与 where 子句不匹配的所有行。在您的示例中，如果您尝试取消 where 子句，您会看到它只添加了几行 - 带有i.DUNS = 'HGBU%'

您需要 NOT EXISTS 而不是连接。

WITH
    DUNS AS (
        select '%PERSON' as DUNS from DUAL UNION ALL
        select 'PH0%' as DUNS from DUAL UNION ALL
        select 'PH1%' as DUNS from DUAL UNION ALL
        select 'PH2%' as DUNS from DUAL UNION ALL
        select 'PH3%' as DUNS from DUAL UNION ALL
        select 'PH4%' as DUNS from DUAL UNION ALL
        select 'PH5%' as DUNS from DUAL UNION ALL
        select 'PH6%' as DUNS from DUAL UNION ALL
        select 'PH7%' as DUNS from DUAL UNION ALL
        select 'PH8%' as DUNS from DUAL UNION ALL
        select 'PH9%' as DUNS from DUAL UNION ALL
        select 'PH-PERSON' as DUNS from DUAL UNION ALL
        select 'TRIALACCT' as DUNS from DUAL
        ),
    ORGTYPE AS(
        select 'Do Not Assign' as ORGTYPE from DUAL UNION ALL
        select 'HGBU_Store_DIM' as ORGTYPE from DUAL UNION ALL
        select 'HGBU%' as ORGTYPE from DUAL
        ),
    CDQ AS( -- I replaced the CDQ CTE with some dummy data so the query would run
        select 11803270 as registry_id, 'PHtola' as DUNS, 'HGBU_ge' as org_type,
            'HGBU_ge' as tech_org_type, 'HGBU_ge' as apps_org_type
            from dual
        )
select * 
from CDQ g
where not exists (select 1 from DUNS h where g.DUNS like h.DUNS)
  and not exists (select 1 from ORGTYPE i where g.ORG_TYPE like i.ORGTYPE)
  and not exists (select 1 from ORGTYPE i where g.TECH_ORG_TYPE like i.ORGTYPE)
  and not exists (select 1 from ORGTYPE i where g.APPS_ORG_TYPE like i.ORGTYPE)
;

推荐阅读