我正在尝试使用join语句,它根据特定的case语句而有所不同。当字段lane.dest_postal_code_prefix不为空时,我希望连接类似于下面的第一段代码。当字段lane.dest_postal_code_prefix为空时,我希望连接类似于下面的第二个代码块。(注意两种情况在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
;
发布于 2019-06-27 02:13:16
您可以像这样使用一个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
)
在第二种情况下(else部分),条件总是真的,因为它是:
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
https://stackoverflow.com/questions/56778750
复制相似问题