SQL查询仅在所有必要列都存在且不为NULL时返回数据

一尘不染

SQL查询仅在所有必要列都存在且不为NULL时返回数据

sql

ID | Type     | total
1    Purchase   12
1    Return     2
1    Exchange   5
2    Purchase   null
2    Return     5
2    Exchange   1
3    Purchase   34
3    Return     4
3    Exchange   2
4    Purchase   12
4    Exchange   2

以上是样本数据。我要返回的是：

ID | Type     | total
 1    Purchase   12
 1    Return     2
 1    Exchange   5
 3    Purchase   34
 3    Return     4
 3    Exchange   2

因此，如果该字段的总和为空，或者该ID的Purchase，Return和Exchange的值均不存在，请完全忽略该ID。我该怎么做呢？

阅读 225

2021-05-16

共1个答案

一尘不染

您可以使用exists。我认为您打算：

select t.*
from t
where exists (select 1
              from t t2
              where t2.id = t.id and t2.type = 'Purchase' and t2.total is not null
             ) and
      exists (select 1
              from t t2
              where t2.id = t.id and t2.type = 'Exchange' and t2.total is not null
             ) and
      exists (select 1
              from t t2
              where t2.id = t.id and t2.type = 'Return' and t2.total is not null
             );

有一些方法可以“简化”此操作：

select t.*
from t
where 3 = (select count(distinct t2.type)
           from t t2
           where t2.id = t.id and
                 t2.type in ('Purchase', 'Exchange', 'Return') and
                 t2.total is not null
          );

2021-05-16