时间戳

首页 > 解决方案 > 如果列中包含特定值，则删除重复记录

sql-server - 如果列中包含特定值，则删除重复记录

问题描述

假设我有这张桌子：

Student | Task | Result  | Reason
Jim     | 3    | Success | NULL
Jim     | 4    | Success | NULL
John    | 3    | Success | NULL
John    | 2    | Failed  | Task finished too late
Bill    | 5    | Success | NULL
Bill    | 7    | Failed  | Not enough knowledge
Bill    | 6    | Unknown | Asked StackOverflow to do his homework

如果说学生有一个或多个失败或未知的任务，我想在这里过滤掉每个学生的成功任务。如果他们根本没有失败/未知，我只需要看到成功的。

所以我的预期结果是：

Student | Task | Result  | Reason
Jim     | 3    | Success | NULL
Jim     | 4    | Success | NULL
John    | 2    | Failed  | Task finished too late
Bill    | 7    | Failed  | Not enough knowledge
Bill    | 6    | Unknown | Asked StackOverflow to do his homework

我尝试使用 group by 和 MIN(Result) 但无论成功/失败，我最终都会得到一条记录。

标签： sql-servertsql

解决方案

这是通过公用表表达式 (CTE) 引用的窗口函数的一个非常简单的用例：

询问

declare @t table(Student varchar(10),Task int,Result varchar(10),Reason varchar(50));
insert into @t values('Jim' ,3,'Success',NULL),('Jim' ,4,'Success',NULL),('John',3,'Success',NULL),('John',2,'Failed ','Task finished too late'),('Bill',5,'Success',NULL),('Bill',7,'Failed ','Not enough knowledge'),('Bill',6,'Unknown','Asked StackOverflow to do his homework');

with s as
(
    select *
          ,sum(case when Result <> 'Success' then 1 else 0 end) over (partition by Student) as NonSuccesses
    from @t
)
select Student
      ,Task
      ,Result
      ,Reason
from s
where Result <> 'Success'
    or NonSuccesses = 0;

输出

+---------+------+---------+----------------------------------------+
| Student | Task | Result  |                 Reason                 |
+---------+------+---------+----------------------------------------+
| Bill    |    7 | Failed  | Not enough knowledge                   |
| Bill    |    6 | Unknown | Asked StackOverflow to do his homework |
| Jim     |    3 | Success | NULL                                   |
| Jim     |    4 | Success | NULL                                   |
| John    |    2 | Failed  | Task finished too late                 |
+---------+------+---------+----------------------------------------+

推荐阅读