如何判断姓名对是否互为昵称？求SQL查询方案

假设你的输入表名为name_pairs，查找表名为nickname_lookup，以下是适配DuckDB的SQL语句：

SELECT
  np.left,
  np.right,
  CASE
    -- 直接双向昵称关系：一方是另一方的规范名+昵称组合
    WHEN EXISTS (
      SELECT 1
      FROM nickname_lookup nl
      WHERE (nl.canonical_name = np.left AND nl.nickname = np.right)
         OR (nl.canonical_name = np.right AND nl.nickname = np.left)
    ) THEN TRUE
    -- 间接关联：两者共享同一规范名（同为该规范名的昵称）
    WHEN EXISTS (
      SELECT 1
      FROM nickname_lookup nl1
      JOIN nickname_lookup nl2 
        ON nl1.canonical_name = nl2.canonical_name
      WHERE nl1.nickname = np.left
        AND nl2.nickname = np.right
    ) THEN TRUE
    -- 姓名完全相同的情况（按需保留）
    WHEN np.left = np.right THEN TRUE
    ELSE FALSE
  END AS desired
FROM name_pairs np;

逻辑说明 #

1. 直接关系检查：查找表里如果存在left是规范名、right是昵称，或者反过来的记录，判定为互为昵称。
2. 间接关系检查：如果两个姓名都是同一个规范名的昵称（比如jon和john都关联johnathon），也判定为互为昵称。
3. 同名情况：如果两个姓名完全一致，默认判定为TRUE，不需要可以删除该分支。

内容的提问来源于stack exchange，提问作者Nick Crews