一对多关系下展示员工及最多3个地址的SQL查询问题

你之前用三次自连接产生重复记录，是因为没有限制三个连接的地址是不同条目，导致同员工的地址之间产生笛卡尔积——比如Jason有3个地址，会生成3×3×3=27条重复组合的记录。

正确的思路是先给每个员工的地址按顺序编号，再将编号后的地址转成对应的列。下面提供两种通用写法：

方法一：窗口函数 + 条件聚合（适用于绝大多数SQL数据库） #

SELECT 
    e.Name AS Employee,
    MAX(CASE WHEN addr.rn = 1 THEN addr.Address END) AS Address1,
    MAX(CASE WHEN addr.rn = 2 THEN addr.Address END) AS Address2,
    MAX(CASE WHEN addr.rn = 3 THEN addr.Address END) AS Address3
FROM Employee e
LEFT JOIN (
    -- 给每个员工的地址编序号，这里按AddressID排序保证顺序固定，顺序不限的话也可以换其他规则
    SELECT 
        EmployeeID,
        Address,
        ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY AddressID) AS rn
    FROM Address
) addr ON e.EmployeeID = addr.EmployeeID
GROUP BY e.EmployeeID, e.Name
ORDER BY e.EmployeeID;

方法二：使用PIVOT（适用于支持PIVOT语法的数据库，如SQL Server、Oracle） #

SELECT 
    Name AS Employee,
    [1] AS Address1,
    [2] AS Address2,
    [3] AS Address3
FROM (
    SELECT 
        e.Name,
        a.Address,
        ROW_NUMBER() OVER(PARTITION BY e.EmployeeID ORDER BY a.AddressID) AS rn
    FROM Employee e
    LEFT JOIN Address a ON e.EmployeeID = a.EmployeeID
) src
PIVOT (
    MAX(Address)
    FOR rn IN ([1], [2], [3])
) pvt
ORDER BY EmployeeID;

两种方法的核心都是通过ROW_NUMBER()窗口函数，按员工分组给每个地址分配唯一序号（1、2、3...），再通过条件聚合或PIVOT将序号对应的地址转换成横向列，这样每个员工只会返回一行，最多显示3个地址，无对应地址的列自动填充为NULL。

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