请求编写验证国家与州关联的Redshift SQL查询

数据表结构 #

1. COUNTRY表 #

2. STATE表 #

3. LOCATION表 #

分类规则 #

• 若LOCATION中的州名称在STATE表中存在，且对应的国家代码与STATE表映射一致，则保留原位置名称；
• 若LOCATION中的州名称在STATE表中存在，但国家代码为空，则保留原位置名称；
• 若LOCATION中的州名称在STATE表中存在，但映射的国家代码不一致，则标记为INVALID；
• 若LOCATION中的州名称在STATE表中不存在，则保留原位置名称；
• 若LOCATION中无州名称，则标记为UNDEFINED。

Redshift SQL查询语句 #

SELECT
    l.Name,
    CASE
        -- 无州名称的情况
        WHEN l.StateName IS NULL THEN 'UNDEFINED'
        -- 州存在但国家代码不匹配的情况
        WHEN s.CountryCode IS NOT NULL AND l.CountryCode IS NOT NULL AND s.CountryCode != l.CountryCode THEN 'INVALID'
        -- 其他所有情况保留原名称
        ELSE l.Name
    END AS "Derived Name"
FROM LOCATION l
LEFT JOIN STATE s 
    ON l.StateName = s.StateName;

期望输出 #

内容的提问来源于stack exchange，提问作者Kalpit Joshi