SQL查询仅在所有必要列都存在且不为NULL时返回数据 [英] SQL query to return data only if ALL necessary columns are present and not NULL

问题描述

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

上面是示例数据.我想返回的是:

Above is sample data. What I want to return is:

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.我该怎么做呢?

So if a field is null in total or the values of Purchase, Return and Exchange are not all present for that ID, ignore that ID completely. How can I go about doing this?

推荐答案

您可以使用exists.我认为您打算这样做:

You can use exists. I think you intend:

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
             );

有一些方法可以简化"此操作:

There are ways to "simplify" this:

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
          );

这篇关于SQL查询仅在所有必要列都存在且不为NULL时返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！