SQL Server 2014多条件查询需求：按规则筛选站点联系人

没问题！针对你这个SQL Server 2014的多条件联系人筛选需求，完全可以用单条查询实现——核心思路是借助窗口函数给每条记录分配优先级排序，然后取每个站点的最高优先级记录。下面一步步拆解：

筛选规则 #

• 规则1：活跃站点优先选取Contact Role为'RO'的联系人；
• 规则2：非活跃站点优先选取Contact Role为'Owner'的联系人；
• 规则3：活跃站点无'RO'联系人时，选取'Owner'联系人；
• 规则4：非活跃站点无'Owner'联系人时，选取'RO'联系人；
• 规则5：若站点无'RO'或'Owner'联系人，选取'Operator'联系人；
• 规则6：活跃站点需排除Contact为'XYZ'且Role为'RO'的记录，优先选其他'RO'联系人，无则选'Owner'或'Operator'；
• 规则7：无任何联系人的站点，Contact字段填NULL。

Source 数据集 #

期望 Final 结果集 #

解决方案SQL #

WITH RankedContacts AS (
    SELECT 
        Site_Status,
        Site_id,
        Site_Contact,
        [Contact Role] AS ContactRole,
        -- 核心：根据规则生成优先级排序值，数字越小优先级越高
        ROW_NUMBER() OVER (
            PARTITION BY Site_id 
            ORDER BY 
                -- 先排除规则6的无效记录，把它们排到最后
                CASE WHEN Site_Status = 'Active' AND Site_Contact = 'XYZ' AND [Contact Role] = 'RO' THEN 2 ELSE 1 END,
                -- 处理活跃站点的优先级：RO > Owner > Operator
                CASE WHEN Site_Status = 'Active' THEN
                    CASE [Contact Role]
                        WHEN 'RO' THEN 1
                        WHEN 'Owner' THEN 2
                        WHEN 'Operator' THEN 3
                        ELSE 4
                    END
                -- 处理非活跃站点的优先级：Owner > RO > Operator
                ELSE
                    CASE [Contact Role]
                        WHEN 'Owner' THEN 1
                        WHEN 'RO' THEN 2
                        WHEN 'Operator' THEN 3
                        ELSE 4
                    END
                END,
                -- 确保无联系人的记录排最后（如果有的话）
                CASE WHEN Site_Contact IS NULL OR [Contact Role] IS NULL THEN 2 ELSE 1 END
        ) AS PriorityRank
    FROM YourSourceTable -- 替换成你的实际表名
)
SELECT 
    Site_Status,
    Site_id,
    ISNULL(Site_Contact, NULL) AS Site_Contact,
    ISNULL(ContactRole, NULL) AS ContactRole
FROM RankedContacts
WHERE PriorityRank = 1
ORDER BY Site_id;

代码逻辑说明 #

1. CTE RankedContacts：用ROW_NUMBER()按Site_id分组，给每个站点的联系人按规则分配优先级排名：
   • 首先处理规则6：把活跃站点中Contact='XYZ'且Role='RO'的记录优先级设为最低，确保不会被选中；
   • 然后根据站点活跃状态分配角色优先级：
     • 活跃站点：RO（1）> Owner（2）> Operator（3），对应规则1、3、5；
     • 非活跃站点：Owner（1）> RO（2）> Operator（3），对应规则2、4、5；
   • 最后把无联系人的记录排到最后，确保有联系人的记录优先被选中；
2. 主查询：筛选每个站点PriorityRank=1的记录（最高优先级联系人），同时用ISNULL()处理无联系人的情况（规则7）。

这个方案没有嵌套复杂子查询，用CTE+窗口函数实现，对于20K条数据的性能完全没问题，SQL Server 2014也完美支持这些语法。

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