Oracle SQL条件连接 [英] Oracle SQL conditional join

问题描述

我有以下SQL语句:

select
    DOCUMENT.DOCUMENT_ID,
    (case
        when DOCUMENT.CLASSIFICATION_CODE is not null
        then DOCUMENT.CLASSIFICATION_CODE
        else TEMPLATE.CLASSIFICATION_CODE end) CLASSIFICATION_CODE,
    CLASSIFICATION.NAME CLASSIFICATION_NAME
from
    DOCUMENT,
    TEMPLATE,
    CLASSIFICATION
where
    DOCUMENT.TEMPLATE_ID = TEMPLATE.TEMPLATE_ID and
    DOCUMENT.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+)

我想做的是:

if DOCUMENT.CLASSIFICATION_CODE IS NOT NULL THEN 
  DOCUMENT.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+),
ELSE
  TEMPLATE.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+)

这可能吗?

推荐答案

您可以使用 NVL 或更标准的 COALESCE :

You can use NVL or the more standard COALESCE for that:

COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE)
      = CLASSIFICATION.CLASSIFICATION_CODE(+)

使用ANSI语法:

select
    DOCUMENT.DOCUMENT_ID,
    COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE) 
        CLASSIFICATION_CODE,
    CLASSIFICATION.NAME CLASSIFICATION_NAME
from
    DOCUMENT
inner join 
    TEMPLATE on DOCUMENT.TEMPLATE_ID = TEMPLATE.TEMPLATE_ID
left join
    CLASSIFICATION on
         COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE) 
       = CLASSIFICATION.CLASSIFICATION_CODE

这篇关于Oracle SQL条件连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！