SQL条件AND

本文介绍了SQL条件AND的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想根据当前行的值确定AND子句.例如，我的工作表有4个日期列:offer_date，accepted_date，start_date，reported_date.我想根据日期查询汇率.我知道reported_date永远不会为空，但这是我的最后选择，因此我有一个优先顺序要加入到exchange_rate表中.我不确定如何使用CASE语句执行此操作，即使那是正确的方法.

I want to determine the AND clause based on the value of the current row. So for example, my job table has 4 date columns: offer_date, accepted_date, start_date, reported_date. 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.

SELECT * FROM job j INNER JOIN exchange_rate er ON j.currency_id = er.currency_id AND er.date = ( -- use offer_date if not null -- use accepted_date if above is null -- use start_date if above two are null -- use reported_date if above three are null )

推荐答案

应该只是一个简单的合并，所以类似:

Should just be a simple coalesce, so something like:

SELECT * FROM job j INNER JOIN exchange_rate er ON j.currency_id = er.currency_id AND er.date = COALESCE( offer_date, accepted_date, start_date, reported_date )