SQL条件连接列 [英] SQL Conditional JOIN column
问题描述
我想根据当前行的值确定JOIN列.
I want to determine the JOIN column based on the value of the current row.
例如,我的工作表有4个日期列:offer_date,accepted_date,start_date,reported_date.
So for example, my job table has 4 date columns: offer_date, accepted_date, start_date, reported_date.
我想根据日期检查汇率.我知道reported_date永远不会为空,但这是我的最后选择,因此我有一个优先顺序要加入到exchange_rate表中.我不确定如何使用CASE语句执行此操作,即使那是正确的方法.
I want to check against an exchange rate based on the date. I know the reported_date is never null, but it's my last resort, so I have a priority order for which to join against the exchange_rate table. I'm not quite sure how to do this with a CASE statement, if that's even the right approach.
INNER JOIN exchange_rate c1 ON c1.date = {{conditionally pick a column here}}
-- use j.offer_date if not null
-- use j.accepted_date if above is null
-- use j.start_date if above two are null
-- use j.reported_date if above three are null
推荐答案
尝试这样的逻辑:
INNER JOIN
exchange_rate c1
ON c1.date = coalesce(j.offer_date, j.accepted_date, j.start_date, j.reported_date)
coalesce()
函数返回列表中的第一个非NULL值.
The coalesce()
function returns the first non-NULL value in the list.
这篇关于SQL条件连接列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!