SQL Server：如何从表中删除部分空行

Question

我是一位有帮助的助手，可以为您翻译文本。

我有一个奇怪的情况，在谷歌上找不到解决方案或类似于stackoverflow的问题：

这是我的表格：

table1（目前）

category col2  col3  col4  col5  col6  col7  col8  totalsum
--------------------------------------------------------------
a         12    25   null  null  null  null   null   $20
a         12    25    34   null  null  null   null   $20
b         57    93    72   63     99   null   null   $50
b         57    93    72   63     99   107    null   $50
b         57    93    72   63     99   107    32     $50

我需要删除部分/不完整的行，并将更完整的行保存到一个新表中，使我的表现在变成这样（我需要保留空值最少的行），但我在过滤不完整的行和隔离更完整的行方面遇到了麻烦。

我需要什么。

category col2  col3  col4  col5  col6  col7  col8  totalsum
--------------------------------------------------------------
a         12    25    34   null  null  null   null   $20
b         57    93    72   63     99   107    32     $50

有人有任何想法吗？

谢谢

- holy_guacamole

---

每个类别的最新行是否总是比同一类别的先前行具有更多的细节？ - chindirala sampath kumar

不，那不是一个要求。 - holy_guacamole

Answer 1

使用 GROUP BY：

SELECT category,
       MAX(col2) AS col2,
       MAX(col3) AS col3,
       MAX(col4) AS col4,
       MAX(col5) AS col5,
       MAX(col6) AS col6,
       MAX(col7) AS col7,
       MAX(col8) AS col8,
       MAX(totalsum) AS totalsum
FROM yourTable
GROUP BY category

假设您可以接受每个类别、每列中最大的非空值，这种方法将非常有效。这种透视技巧之所以奏效，是因为SQL Server中的MAX()函数忽略NULL值。

- Tim Biegeleisen

---

如果类别的最新一行修改了相同类别的前一行所具有的某列数值，且该数值变小了，那会怎么样？ - chindirala sampath kumar

那么这种简单的方法就会失败。但是OP的数据是否意味着会出现这种情况？ - Tim Biegeleisen

Answer 2

可能是这样的：

select * from table1 as t1 
where not exists(
                select 1 from table1 as t2 
                where(t1.id!=t2.id)and(t1.category=t2.category)
                and((t1.col1=t2.col1)or(is_null(t1.col1)and(not is_null(t2.col1))and...
                )

但是，如果您要从t1和t2中排除相同的行，则每行必须具有唯一的ID，例如（t1.id！= t2.id）。 主要思想-排除具有更多相关副本（更多非空字段）的行。

- Dmitry D

Answer 3

您可以尝试使用row_number()函数对这些列进行排序：

select *
from (
select *
, ROW_NUMBER() OVER (PARTITION BY category ORDER BY col2 desc, col3 desc, col4 desc, col5 desc, col6 desc, col7 desc, col8 desc) rn
from table1) i
where i.rn = 1

测试代码：

;WITH cte AS
(
SELECT 'a' category  ,12 col2 ,25 col3, null col4, null col5, null col6 ,null col7, null col8, 20 totalsum
UNION ALL SELECT 'a' ,12 ,25 ,34 ,null ,null ,null ,null ,20
UNION ALL SELECT 'b' ,57 ,93 ,72 ,63 ,99 ,null ,null ,50
UNION ALL SELECT 'b' ,57 ,93 ,72 ,63 ,99 ,107 ,null ,50
UNION ALL SELECT 'b' ,57 ,93 ,72 ,63 ,99 ,107 ,32 ,50
)
SELECT *
FROM (
SELECT *
, ROW_NUMBER() OVER (PARTITION BY category ORDER BY col2 DESC, col3 DESC, col4 DESC, col5 DESC, col6 DESC, col7 DESC, col8 DESC) rn
FROM cte) i
WHERE i.rn = 1

- artm