根据地址优先级从多地址中为员工选取唯一地址的SQL问题

我来帮你搞定这个问题～先说说你原来的SQL为啥不行：它的case语句完全没起到优先级筛选的作用，只是在判断地址类型等于自身，相当于没加这个条件，所以会返回该员工所有有效地址，而不是按优先级选出唯一的那条；而且关联表后没做按员工分组取最高优先级地址的处理，结果肯定会有多条记录，不符合你“每个员工唯一地址”的需求。

下面给你两种可行的解决方案，你可以根据自己用的数据库（比如MySQL 8+、SQL Server、PostgreSQL等）选合适的：

方案一：窗口函数法（支持窗口函数的数据库） #

这个方法简洁直观，先给地址按优先级打分，再用窗口函数给每个员工的地址排序，取排序第一的那条：

WITH EmpAddressWithPriority AS (
    SELECT 
        e.Id AS EmpId,
        e.Name,
        a.AddressId,
        a.AddressType,
        -- 给地址类型分配优先级分数：a最高（3分），其次c（2分），最后b（1分）
        CASE a.AddressType
            WHEN 'a' THEN 3
            WHEN 'c' THEN 2
            WHEN 'b' THEN 1
            ELSE 0
        END AS PriorityScore,
        -- 按员工分组，按优先级分数降序排序，每个员工的地址会得到1、2、3...的序号
        ROW_NUMBER() OVER (PARTITION BY e.Id ORDER BY 
            CASE a.AddressType
                WHEN 'a' THEN 3
                WHEN 'c' THEN 2
                WHEN 'b' THEN 1
                ELSE 0
            END DESC) AS RowNum
    FROM Emp e
    INNER JOIN EmpAddress ea ON e.Id = ea.EmpId
    INNER JOIN Address a ON a.AddressId = ea.AddressId
    WHERE a.AddressType IS NOT NULL
)
SELECT EmpId, Name, AddressId, AddressType
FROM EmpAddressWithPriority
WHERE RowNum = 1; -- 只取每个员工优先级最高的那条地址

方案二：子查询筛选法（兼容老版本数据库，如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用子查询来筛选每个员工的最高优先级地址：

SELECT 
    e.Id AS EmpId,
    e.Name,
    a.AddressId,
    a.AddressType
FROM Emp e
INNER JOIN EmpAddress ea ON e.Id = ea.EmpId
INNER JOIN Address a ON a.AddressId = ea.AddressId
WHERE a.AddressType IS NOT NULL
AND (
    -- 逻辑：优先选a；没有a时选c；没有a和c时选b
    a.AddressType = 'a'
    OR (
        a.AddressType = 'c'
        AND NOT EXISTS (SELECT 1 FROM EmpAddress ea2 
                        INNER JOIN Address a2 ON ea2.AddressId = a2.AddressId
                        WHERE ea2.EmpId = e.Id AND a2.AddressType = 'a')
    )
    OR (
        a.AddressType = 'b'
        AND NOT EXISTS (SELECT 1 FROM EmpAddress ea2 
                        INNER JOIN Address a2 ON ea2.AddressId = a2.AddressId
                        WHERE ea2.EmpId = e.Id AND a2.AddressType IN ('a','c'))
    )
)
GROUP BY e.Id, e.Name, a.AddressId, a.AddressType; -- 确保每个员工只返回一条记录

测试你的样本数据 #

用你提供的样本数据测试的话，员工ID=1有a、b、c三个地址，两种方案都会返回地址类型为a的记录；如果某个员工只有b地址，就会返回b；如果有c和b但没有a，就会返回c，完全符合你的需求。

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