SQL实现优先按3RD_PARTY_ID、次按EMAIL匹配获取ACCOUNT_ID的方法

核心逻辑 #

需求要求两个匹配优先级：

• 第一优先级：ACCOUNT_TABLE.3RD_PARTY_ID = MATCHING_TABLE.MATCH_3RD_ID
• 第二优先级：第一优先级匹配失败时，走ACCOUNT_TABLE.EMAIL = MATCHING_TABLE.MATCH_EMAIL
• 两次都匹配失败时ACCOUNT_ID返回空值，且保留所有MATCHING_TABLE的记录

实现代码 #

SELECT
  m.MATCH_#,
  COALESCE(a1.ACCOUNT_ID, a2.ACCOUNT_ID) AS ACCOUNT_ID
FROM MATCHING_TABLE m
LEFT JOIN ACCOUNT_TABLE a1
  ON m.MATCH_3RD_ID = a1.3RD_PARTY_ID
LEFT JOIN ACCOUNT_TABLE a2
  ON m.MATCH_EMAIL = a2.EMAIL
-- 可选：如果存在单条匹配规则匹配到多个账号的场景，可添加以下逻辑去重
GROUP BY m.MATCH_#, COALESCE(a1.ACCOUNT_ID, a2.ACCOUNT_ID)

验证说明 #

针对你给出的示例数据：

• MATCH_#=zz1的记录，通过第一优先级匹配到3RD_PARTY_ID=AAA的ACCOUNT_ID=001，最终返回001
• MATCH_#=zz2的记录，第一优先级无匹配，走第二优先级匹配到EMAIL=bob@bob.com的ACCOUNT_ID=002，最终返回002
• MATCH_#=zz3的记录两次匹配都无结果，ACCOUNT_ID返回空值
  完全符合预期输出要求。

内容的提问来源于stack exchange，提问作者Geoff Garcia