CASE WHEN语句中存在多个条件

Question

我在SQL(Amazon Redshift(中使用招聘数据，其中每个申请者都有多个来源，我将其拆分为不同的列，称为source_1、source_2和source_3，以及一个number_of_sources列，以记录申请者有多少来源。我们希望每个申请人只有一个来源，并且在如何选择来源时要遵守某些规则，我正试图写一个CASE WHEN语句来实现这一点。我目前遇到一个错误，但我不知道为什么。

我的代码是：

CASE WHEN number_of_sources = 1
THEN source_1
ELSE WHEN number_of_sources = 2
and source_1 in ('Email Applicant', 'Job site', 'Added manually') THEN source_2
ELSE WHEN number_of_sources = 2
and source_2 in ('Email Applicant', 'Job site', 'Added manually') THEN source_1
ELSE WHEN number_of_sources = 3
and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
and source_2 in ('Email Applicant', 'Job site', 'Added manually')  THEN source_3
ELSE WHEN number_of_sources = 3
and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
and source_3 in ('Email Applicant', 'Job site', 'Added manually')  THEN source_2
ELSE WHEN number_of_sources = 3
and source_3 in ('Email Applicant', 'Job site', 'Added manually') 
and source_2 in ('Email Applicant', 'Job site', 'Added manually')  THEN source_1
ELSE 'Blank'    END                                                                             as source_final
from staging.candidates_by_source

希望这是有道理的，任何建议都将不胜感激！非常感谢

Answer 1

删除除最后一个之外的所有其他内容

CASE WHEN number_of_sources = 1
THEN source_1
WHEN number_of_sources = 2
and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
THEN source_2
WHEN number_of_sources = 2
and source_2 in ('Email Applicant', 'Job site', 'Added manually') 
THEN source_1
WHEN number_of_sources = 3
and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
and source_2 in ('Email Applicant', 'Job site', 'Added manually') 
THEN source_3
WHEN number_of_sources = 3
and source_1 in ('Email Applicant', 'Job site', 'Added manually') 
and source_3 in ('Email Applicant', 'Job site', 'Added manually')
THEN source_2
WHEN number_of_sources = 3
and source_3 in ('Email Applicant', 'Job site', 'Added manually') 
and source_2 in ('Email Applicant', 'Job site', 'Added manually')
THEN source_1
ELSE 'Blank'    END                                                                               as source_final
from staging.candidates_by_source