SQL 过滤掉不太具体的行

我的表数据看起来像

 Col1  | Col2 | Col3
    1  |   2  | NULL
    1  |   2  | 3
    1  | NULL | NULL
    1  |   5  | NULL
    2  | NULL | NULL  

我想编写一个查询，以便只获得最具体的条目。 IE。在上面的示例中，row1 比 row3 更具体，因为“Col1”的值在两者中相同，但“Col2”中的值在 row1 中更具体（非空），类似地，row2 比 row1 更具体。

对于上述数据集，结果应如下所示：

Col1 | Col2 | Col3
  1  |  2   |  3
  1  |  5   | NULL
  2  | NULL | NULL  

NOTE：列的数据类型可以是任何类型。

我假设这些列是“有序”的，因为它们在您的查询中，所以您不会遇到这样的情况col2为空并且col3不为空：

select col1, col2, col3
from table t
where (col3 is not null) or
      (col3 is null and col2 is not null and
       not exists (select 1
                   from table t2
                   where t2.col1 = t.col1 and t2.col2 = t.col2 and t2.col3 is not null
                  )
      ) or
      (col2 is null and col1 is not null and
       not exists (select 1
                   from table t2
                   where t2.col1 = t.col1 and t2.col2 is not null
                  )
      );

这背后的逻辑是：

1. 将所有行取到哪里col3不为空。
2. 将所有行取到哪里col2不为空并且不存在具有以下值的相似行col3.
3. 将所有行取到哪里col1不为空并且不存在具有以下值的相似行col2.

EDIT:

在 Oracle 中，您可以更简单地执行此操作：

select col1, col2, col3
from (select t.*,
             max(col3) over (partition by col1, col2) as maxcol3,
             max(col2) over (partition by col1) as maxcol2
      from table t
     ) t
where (col3 is not null) or
      (col2 is not null and maxcol3 is null) or
      (col1 is not null and maxcol2 is null);

编辑二： （对“更具体”有明确的定义。）

我认为这就是逻辑的推断。它需要查看所有组合：

select col1, col2, col3
from (select t.*,
             max(col3) over (partition by col1, col2) as maxcol3_12,
             max(col2) over (partition by col1, col3) as maxcol2_13,
             max(col1) over (partition by col2, col3) as maxcol1_23,
             max(col1) over (partition by col1) as maxcol1_2,
             max(col1) over (partition by col2) as maxcol1_3,
             max(col2) over (partition by col1) as maxcol2_1,
             max(col2) over (partition by col3) as maxcol2_3,
             max(col3) over (partition by col2) as maxcol3_1,
             max(col3) over (partition by col2) as maxcol3_2,
      from table t
     ) t
where (col1 is not null and col2 is not null and col3 is not null) or
      (col1 is not null and col2 is not null and maxcol3 is null) or
      (col1 is not null and col3 is not null and maxcol2 is null) or
      (col2 is not null and col1 is not null and maxcol3 is null) or
      (col2 is not null and col3 is not null and maxcol1 is null) or
      (col3 is not null and col1 is not null and maxcol2 is null) or
      (col3 is not null and col2 is not null and maxcol1 is null) or
      (col1 is not null and maxcol2 is null and maxcol3 is null) or
      (col2 is not null and maxcol1 is null and maxcol3 is null) or
      (col3 is not null and maxcol1 is null and maxcol2 is null);

第一个组合表示“如果所有值都不为空，则保留此行”。第二个说：“如果 col1 和 col2 不为空并且 col3 永远没有值，则保留此行”。依此类推，直到最后一行：“保持这一行 col3 不为空，并且 col1 和 col2 永远不会有值”。

这可能会简化为：

where not ((col1 is null and maxcol1 is not null) or
           (col2 is null and maxcol2 is not null) or
           (col3 is null and maxcol3 is not null)
          );