如何编写复杂SQL查询处理用户多服务状态筛选需求？

嘿，我来帮你搞定这个有点绕的SQL筛选需求！先把核心逻辑拆解清楚，再一步步给出可落地的解决方案～

问题背景 #

我们有一张记录用户服务信息的表，包含服务状态、服务区域（Service_Area）、服务领域（Service_Sector）以及最后修改日期。用户可以同时参与多个服务，现在需要精准筛选出符合特定规则的INACTIVE状态服务记录。

核心筛选规则 #

所有筛选都基于Loc_Code、User_Id、Service_Area、Service_Sector这个组合维度：

• 如果该组合下所有服务都是INACTIVE：
  • 若组合内服务数量>1，只保留最后修改（Last_Changed最新）的那条记录
  • 若组合内只有1条服务，直接选中这条记录
• 如果该组合下存在至少1个ACTIVE状态的服务，则这个组合下的所有记录都不纳入结果

场景说明 #

• 场景1：某组合下有5个服务且均为INACTIVE → 选取最后修改的记录
• 场景2：某组合下5个服务中至少有一个为ACTIVE → 不选取该组合下的任何数据
• 场景3：某组合下仅有一个服务且状态为INACTIVE → 选取该记录

表结构与示例数据 #

表结构说明 #

Loc_Code、User_Id、Service_Area、Service_Sector、Service的组合是唯一键；同一Loc_Code、User_Id、Service_Area、Service_Sector组合下可以存在多个不同的服务。

示例表 #

预期结果表 #

SQL解决方案与解释 #

我用CTE（公共表表达式）来分步处理，逻辑更清晰易懂，替换your_table_name为你的实际表名即可：

WITH service_group_stats AS (
    -- 第一步：统计每个组合的核心指标：是否有ACTIVE服务、服务总数
    SELECT 
        Loc_Code,
        User_Id,
        Service_Area,
        Service_Sector,
        COUNT(*) AS total_services,
        MAX(CASE WHEN Status = 'ACTIVE' THEN 1 ELSE 0 END) AS has_active
    FROM 
        your_table_name
    GROUP BY 
        Loc_Code, User_Id, Service_Area, Service_Sector
),
ranked_inactive_services AS (
    -- 第二步：对符合条件的组合（无ACTIVE）的INACTIVE记录按修改日期排序
    SELECT 
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY t.Loc_Code, t.User_Id, t.Service_Area, t.Service_Sector 
            ORDER BY t.Last_Changed DESC
        ) AS rn
    FROM 
        your_table_name t
    JOIN 
        service_group_stats s 
        ON t.Loc_Code = s.Loc_Code
        AND t.User_Id = s.User_Id
        AND t.Service_Area = s.Service_Area
        AND t.Service_Sector = s.Service_Sector
    WHERE 
        t.Status = 'INACTIVE'
        AND s.has_active = 0  -- 只保留没有ACTIVE服务的组合
)
-- 第三步：筛选每个组合中排名第一的记录（最新的/唯一的那条）
SELECT 
    Loc_Code, User_Id, Service_Area, Service_Sector, Service, Status, Last_Changed
FROM 
    ranked_inactive_services
WHERE 
    rn = 1;

代码逻辑拆解 #

1. service_group_stats CTE：按组合分组，统计每个组合的服务总数，以及是否存在ACTIVE服务（只要有一个ACTIVE，has_active就会是1）。
2. ranked_inactive_services CTE：关联原表和统计结果，只保留INACTIVE记录，同时对每个符合条件的组合按Last_Changed降序排名，最新的记录排第1。
3. 最终查询：筛选出排名为1的记录，完美满足“多服务选最新，单服务直接选”的要求。

把示例数据代入这个SQL，就能得到预期的3条结果，完全符合场景规则～

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