基于优先级规则关联Customers与Addresses表并返回单条客户记录

关联规则 #

• 优先选取isPrimary为1的主地址；
• 若无主地址，则选取address_line以字母'A'开头的地址（多条仅取一条）；
• 若上述条件均不满足，则地址相关字段留空。

Customers表结构 #

Addresses表结构 #

预期输出 #

规则说明 #

• John有两个以'A'开头的地址，但存在主地址，故选取主地址；
• Mike的3个地址均非主地址，故选取以'A'开头的地址；
• Tim有两个主地址，仅选取第一条；
• Bart的地址既非主地址也不以'A'开头，故地址字段留空。

解决方案（SQL） #

使用窗口函数ROW_NUMBER()按优先级为每个客户的地址排序，再关联主表获取结果：

WITH ranked_addresses AS (
    SELECT 
        customer_id,
        address_line,
        city,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY 
                -- 优先级1：主地址排最前
                CASE WHEN isPrimary = 1 THEN 0 ELSE 1 END,
                -- 优先级2：以A开头的地址次之
                CASE WHEN address_line LIKE 'A%' THEN 0 ELSE 1 END,
                -- 同优先级下取address_id最小的记录
                address_id
        ) AS rn
    FROM Addresses
)
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    -- 无匹配地址时用空字符串填充
    COALESCE(ra.address_line, '') AS address_line,
    COALESCE(ra.city, '') AS city
FROM Customers c
LEFT JOIN ranked_addresses ra 
    ON c.customer_id = ra.customer_id AND ra.rn = 1;

代码解释 #

1. CTE ranked_addresses：为每个客户的地址分配排序号rn，排序规则严格遵循需求：主地址优先，其次是A开头的地址，同级别地址按address_id取最早的一条。
2. 主查询：左连接Customers表和排序后的地址表，只取每个客户的第一条排序记录（rn=1），用COALESCE将无匹配地址的字段转为空字符串，符合预期输出要求。

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