SQL Server中同一ID仅保留指定Type行的实现问询

嘿，我明白你的需求了——你要筛选出状态为active（ID=1）和active-busy（ID=2）的客户，但同一个客户ID如果同时存在Type 1和Type 3的记录时，得优先保留Type 3的那条。你的CASE语句思路方向是对的，但可以用更清晰、可维护的方式实现，我给你两个靠谱的方案：

方法一：使用窗口函数（推荐，扩展性强） #

窗口函数可以轻松给每个客户的记录按优先级排序，然后只保留最高优先级的那条记录，逻辑清晰且适合后续扩展更多优先级规则：

WITH ranked_customer_records AS (
    SELECT 
        corel.*,
        -- 给Type 3最高优先级（编号0），其他Type编号1，确保优先选Type3
        ROW_NUMBER() OVER (
            PARTITION BY corel.objid 
            ORDER BY CASE WHEN corel.opts = 3 THEN 0 ELSE 1 END ASC
        ) AS priority_rank
    FROM corel
    -- 先过滤出目标状态的记录：active（ID=1）和active-busy（ID=2）
    WHERE corel.type IN (1, 2)
)
-- 只保留每个客户优先级最高的那条记录
SELECT *
FROM ranked_customer_records
WHERE priority_rank = 1;

逻辑说明： #

• PARTITION BY corel.objid：按客户ID分组，确保每个客户的记录单独排序
• ORDER BY CASE ...：自定义排序规则，把opts=3的记录排在最前面
• ROW_NUMBER()：给每个分组内的记录按顺序编号，priority_rank=1就是每个客户的优先记录

方法二：使用子查询判断存在性 #

如果你对窗口函数不太熟悉，也可以用EXISTS子查询来判断同一客户是否存在Type3的记录，进而筛选目标行：

SELECT c.*
FROM corel c
WHERE c.type IN (1, 2)
AND (
    -- 如果该客户存在opts=3的记录，只保留opts=3的行
    (EXISTS (SELECT 1 FROM corel c2 WHERE c2.objid = c.objid AND c2.opts = 3) AND c.opts = 3)
    -- 如果该客户没有opts=3的记录，保留原本符合状态要求的行
    OR NOT EXISTS (SELECT 1 FROM corel c2 WHERE c2.objid = c.objid AND c2.opts = 3)
);

逻辑说明： #

• 外层WHERE先过滤出状态为1和2的记录
• 内层EXISTS判断当前客户是否有opts=3的记录，分两种情况筛选：有则只留opts=3，无则留原记录

这两种方法都能完美实现你的需求，窗口函数的方案更适合未来可能增加更多优先级规则的场景，子查询的方案则更直观易懂。

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