时间戳

首页 > 解决方案 > TSQL - 过滤组，如果它包含所有空

sql - TSQL - 过滤组，如果它包含所有空

问题描述

我想过滤掉包含 TASK_START 和 TASK_END 的所有空值的组，即 B 和 D。

样本表数据

+----+-------+-------------------------+-------------------------+-------------------------+-------------------------+
| ID | STATE |       ENTER_STATE       |       LEAVE_STATE       |       TASK_START        |        TASK_END         |
+----+-------+-------------------------+-------------------------+-------------------------+-------------------------+
| A  | UP    | 2018-11-11 08:00:00.000 | 2018-11-11 08:30:00.000 | 2018-11-11 08:00:00.000 | 2018-11-11 08:10:00.000 |
| A  | UP    | 2018-11-11 09:00:00.000 | 2018-11-11 09:30:00.000 | NULL                    | NULL                    |
| A  | UP    | 2018-11-11 10:00:00.000 | 2018-11-11 10:30:00.000 | 2018-11-11 08:20:00.000 | 2018-11-11 08:30:00.000 |
| B  | UP    | 2018-11-11 08:00:00.000 | 2018-11-11 09:00:00.000 | NULL                    | NULL                    |
| B  | UP    | 2018-11-11 09:00:00.000 | 2018-11-11 10:00:00.000 | NULL                    | NULL                    |
| B  | UP    | 2018-11-11 10:20:00.000 | 2018-11-11 11:00:00.000 | NULL                    | NULL                    |
| B  | UP    | 2018-11-11 11:00:00.000 | 2018-11-11 12:00:00.000 | NULL                    | NULL                    |
| C  | UP    | 2018-11-11 08:00:00.000 | 2018-11-11 08:20:00.000 | 2018-11-11 08:15:00.000 | 2018-11-11 08:30:00.000 |
| C  | UP    | 2018-11-11 08:20:00.000 | 2018-11-11 08:30:00.000 | 2018-11-11 08:20:00.000 | 2018-11-11 08:35:00.000 |
| D  | UP    | 2018-11-11 08:00:00.000 | 2018-11-11 08:10:00.000 | NULL                    | NULL                    |
| D  | UP    | 2018-11-11 08:10:00.000 | 2018-11-11 09:10:00.000 | NULL                    | NULL                    |
+----+-------+-------------------------+-------------------------+-------------------------+-------------------------+

标签： sqlsql-servertsqlsql-server-2012

解决方案

您可以使用not exists：

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.id = t.id and t1.task_start is not null and t1.task_end is not null
                 );

其他选项将使用GROUP BY ：

select id
from table t
group by id
having sum(case when task_start is not null then 1 else 0  end) = 0 and
       sum(case when task_end is not null then 1 else 0  end) = 0;

推荐阅读