基于地址状态优先级的员工详情SQL查询实现问询

嘿，我来帮你梳理下这段SQL的问题，再给你靠谱的优化方案！

先明确下背景信息 #

Employee表结构 #

-------------- Employee --------------
EmpID  EmpName
1      Abc

Address表结构 #

Address
-----------------------------------
AddressId  EmpID  Address1  Status
-----------------------------------
1          1      Abc 1     Active
2          1      Abc 2     Inactive

你的查询需求规则 #

• 若员工同时拥有Active和Inactive地址，查询显示Active地址；
• 若员工仅拥有Inactive地址，查询显示该Inactive地址；
• 若员工有多个Active或Inactive地址，优先显示Active地址中的第一条，若无则显示Inactive地址中的第一条。

你编写的SQL语句 #

SELECT a.EmpID, a.status, Count(a.Status) as Count,
CASE
WHEN Count(a.Status) = 1 and a.Status In ('Active', 'Inactive') THEN (select top 1 a.Address1 from Address a where a.Status = 'Active')
WHEN a.Status= 'Inactive' and count(a.Status) = 1 THEN (select top 1 a.Address1 from Address a where a.Status = 'Inactive')
WHEN Count(a.Status) > 1 and a.Status In ('Active', 'Inactive') THEN (select top 1 a.Address1 from Address a where a.Status = 'Active')
END AS Address1
from Address a
where Attendee_Id= 1
group by a.Status, Address1, a.EmpId

原SQL存在的几个问题 #

1. 分组逻辑不对：你按Status、Address1、EmpId分组，会把同一个员工的不同地址拆成多条记录，不符合“每个员工只返回一条结果”的核心需求；
2. 表别名冲突：子查询里的表别名也是a，和外部查询的a重名，会导致数据库解析逻辑混乱，结果可能不符合预期；
3. 条件冗余且有漏洞：第一个WHEN和第二个WHEN的条件有重叠，而且没覆盖“多个Inactive但无Active”的场景；
4. 硬编码与字段错误：where Attendee_Id= 1应该是EmpID=1吧？而且如果要查询所有员工，这个硬编码就完全不适用了。

优化后的SQL方案 #

我给你准备了两种方案，分别适配不同的使用场景：

方案一：批量查询所有员工的符合规则地址 #

这个方案用窗口函数来给每个员工的地址排序，逻辑清晰还支持批量查询，非常实用：

WITH RankedAddresses AS (
    SELECT 
        EmpID,
        Address1,
        -- 排序规则：Active优先级最高，同状态下按AddressId升序取第一条
        ROW_NUMBER() OVER (
            PARTITION BY EmpID 
            ORDER BY CASE WHEN Status = 'Active' THEN 0 ELSE 1 END, AddressId
        ) AS AddressRank
    FROM Address
)
SELECT 
    e.EmpID,
    e.EmpName,
    ra.Address1
FROM Employee e
LEFT JOIN RankedAddresses ra 
    ON e.EmpID = ra.EmpID 
    AND ra.AddressRank = 1;

方案二：仅查询指定单个员工（比如EmpID=1） #

如果只需要查某一个员工的信息，用这个简洁版就够了：

SELECT 
    e.EmpName,
    COALESCE(
        -- 优先取Active状态的第一条地址
        (SELECT TOP 1 Address1 FROM Address WHERE EmpID = 1 AND Status = 'Active' ORDER BY AddressId),
        -- 没有Active的话，再取Inactive的第一条地址
        (SELECT TOP 1 Address1 FROM Address WHERE EmpID = 1 AND Status = 'Inactive' ORDER BY AddressId)
    ) AS Address1
FROM Employee e
WHERE e.EmpID = 1;

优化思路说明 #

• 窗口函数方案：通过ROW_NUMBER()给每个员工的地址按规则排名，直接取排名第一的记录，完美覆盖所有需求场景，而且性能也不错；
• COALESCE方案：利用COALESCE函数优先返回第一个非空结果的特性，逻辑简单直接，适合单员工查询；
• 两个方案都避免了原SQL的分组错误和别名冲突，逻辑更严谨，也更符合实际业务需求。

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