SQL一对多关系Inner Join按优先级筛选用户地址的实现方法

你可以通过以下两种基于内连接的方案实现需求，兼容绝大多数主流SQL数据库：

方案1：窗口函数排序法（推荐，代码简洁易维护） #

实现思路 #

给每个用户的关联地址按优先级打序号：Home类型优先级为1，Office类型优先级为2，每个用户仅取序号为1的记录即可保证唯一，同时满足地址优先规则。

代码实现 #

SELECT 
    UserId,
    UserName,
    AddressLinkId,
    AddressType,
    AddressId,
    City,
    ZipCode,
    CountyCode
FROM (
    SELECT
        AU.UserId
       ,AU.UserName
       ,AL.AddressLinkId
       ,AL.AddressType
       ,UA.AddressId
       ,UA.City
       ,UA.ZipCode
       ,UA.CountyCode
       -- 按地址优先级排序，Home优先
       ,ROW_NUMBER() OVER (PARTITION BY AU.UserId ORDER BY CASE AL.AddressType WHEN 'Home' THEN 1 WHEN 'Office' THEN 2 END) AS addr_rank
    FROM AppUser AU
    INNER JOIN AddressLink AL
        ON AU.UserId = AL.UserId
    INNER JOIN UserAddress UA
        ON UA.AddressId = AL.AddressId
) t
WHERE addr_rank = 1

方案2：预聚合优先级匹配法（兼容不支持窗口函数的低版本数据库，比如MySQL5.x） #

实现思路 #

先聚合得到每个用户的最优地址类型，再用内连接关联回原表匹配对应地址记录，确保每个用户仅返回一条。

代码实现 #

SELECT
    AU.UserId
   ,AU.UserName
   ,AL.AddressLinkId
   ,AL.AddressType
   ,UA.AddressId
   ,UA.City
   ,UA.ZipCode
   ,UA.CountyCode
FROM AppUser AU
-- 关联用户最优地址类型
INNER JOIN (
    SELECT 
        UserId,
        -- 优先取Home，没有则取Office
        COALESCE(MAX(CASE WHEN AddressType = 'Home' THEN AddressType END), MAX(CASE WHEN AddressType = 'Office' THEN AddressType END)) AS target_addr_type
    FROM AddressLink
    WHERE AddressType IN ('Home', 'Office')
    GROUP BY UserId
) user_target
    ON AU.UserId = user_target.UserId
-- 关联匹配最优地址
INNER JOIN AddressLink AL
    ON AU.UserId = AL.UserId 
    AND AL.AddressType = user_target.target_addr_type
INNER JOIN UserAddress UA
    ON UA.AddressId = AL.AddressId

注意事项 #

• 如果单个用户存在多条同类型（比如2个Home）地址，两种方案默认仅返回第一条，你可以根据业务需要调整ORDER BY的排序规则，比如按AddressLinkId倒序取最新的地址。
• 两种方案均未引入外连接，完全通过内连接实现需求。

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