如何使用带JOIN的CASE语句

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (207)

我试图使用基于某些case语句而变化的join语句。当字段lane.dest_postal_code_prefix为NOT NULL时,我希望连接类似于下面的第一个代码块。当字段lane.dest_postal_code_prefix为NULL时,我希望连接类似于下面的第二个代码块。(注意两种情况之间ON条件的差异)

我需要一些帮助将case语句添加到join子句中。

--WHEN lane.dest_postal_code_prefix is NOT NULL
SELECT * FROM big_bucket_bridge A
    LEFT JOIN lane 
    ON
    (
    A.customer_country = lane.dest_country_code
    AND
    SUBSTRING( A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix ) ) = 
    lane.dest_postal_code_prefix
        )

WHERE  
    snapshot_day between '2019-06-23'-22 and '2019-06-23'
    AND (is_before_cutoff_g OR (is_before_cutoff_opt_g and not under_two))
    AND row_n =1
;

--WHEN lane.dest_postal_code_prefix is NULL
SELECT * FROM big_bucket_bridge A
    LEFT JOIN lane 
    ON
    (
    A.customer_country = lane.dest_country_code
    )

WHERE  
    snapshot_day between '2019-06-23'-22 and '2019-06-23'
    AND (is_before_cutoff_g OR (is_before_cutoff_opt_g and not under_two))
    AND row_n =1
;
提问于
用户回答回答于

你可以像这样使用一个ON子句:

ON
(
A.customer_country = lane.dest_country_code
AND
COALESCE(SUBSTRING(A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix )), '') = CASE 
  WHEN lane.dest_postal_code_prefix IS NOT NULL THEN lane.dest_postal_code_prefix 
  ELSE COALESCE(SUBSTRING(A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix )), '')
END
)

在第二种情况下(否则部分),条件始终为真,因为它是:

COALESCE(SUBSTRING(A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix )), '') = COALESCE(SUBSTRING(A.ddm_zip, 1, LENGTH( lane.dest_postal_code_prefix )), '')

所以唯一的实际情况是

A.customer_country = lane.dest_country_code

扫码关注云+社区

领取腾讯云代金券