如何构造SQL双表的多优先级 fallback 连接查询？

你可以通过分层LEFT JOIN+COALESCE的方式实现这种优先级连接逻辑，确保优先使用最可靠的匹配条件，依次降级到备选条件。以下是具体实现方案：

核心SQL语句 #

SELECT 
    o.OrderId,
    o.CustomerId AS Order_CustomerId,
    -- 优先取CustomerId匹配的客户ID，其次是地址匹配，最后是姓名匹配
    COALESCE(c1.CustomerId, c2.CustomerId, c3.CustomerId) AS Customer_CustomerId,
    o."Customer FName",
    o."Customer LName",
    COALESCE(c1."First Name", c2."First Name", c3."First Name") AS "Customer First Name",
    COALESCE(c1."Last Name", c2."Last Name", c3."Last Name") AS "Customer Last Name",
    o."Delivery Address",
    COALESCE(c1.Address, c2.Address, c3.Address) AS "Customer Address",
    o."Other Order Info",
    COALESCE(c1.DOB, c2.DOB, c3.DOB) AS Customer_DOB
FROM Orders o
-- 第一优先级：通过CustomerId匹配
LEFT JOIN Customers c1 ON o.CustomerId = c1.CustomerId
-- 第二优先级：仅当CustomerId无匹配时，用地址匹配
LEFT JOIN Customers c2 ON c1.CustomerId IS NULL 
    AND o."Delivery Address" = c2.Address
-- 第三优先级：仅当CustomerId和地址都无匹配时，用姓名匹配
LEFT JOIN Customers c3 ON c1.CustomerId IS NULL 
    AND c2.CustomerId IS NULL 
    AND o."Customer FName" = c3."First Name" 
    AND o."Customer LName" = c3."Last Name"
-- 可选：过滤完全无匹配的订单
WHERE COALESCE(c1.CustomerId, c2.CustomerId, c3.CustomerId) IS NOT NULL

逻辑说明 #

1. 分层连接优先级：
   • 先尝试c1通过CustomerId连接，这是最准确的匹配方式
   • 只有c1无匹配（c1.CustomerId IS NULL）时，才触发c2的地址连接
   • 仅当c1和c2都无匹配时，才触发c3的姓名连接
2. COALESCE函数作用：从多个连接结果中取第一个非空值，确保高优先级匹配的字段被优先选用
3. 字段名处理：注意带空格的字段名需要用对应数据库的标识符包裹（如MySQL用反引号，SQL Server用[]，PostgreSQL用""）

注意事项 #

• 姓名匹配存在固有缺陷，若存在同名客户会导致错误关联，建议仅作为最后的兜底方案
• 如果需要保留完全无匹配的订单，可以去掉最后的WHERE条件，此时未匹配的字段会显示为NULL

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