查找任何列中具有空值的所有行 [英] Find All Rows With Null Value(s) in Any Column

问题描述

我正在尝试创建一个查询，该查询将返回除 1 列之外的所有具有空值的行.某些行在某处将有多个空条目.我想排除一列，因为此时所有条目都为空，并且它是唯一允许具有空值的列.我被卡住了，因为我不知道如何在 WHERE 中包含所有列.

I'm trying to create a query that will return all the rows that have a null value across all but 1 column. Some rows will have more than one null entry somewhere. There is one column I will want to exclude, because at this moment in time all of the entries are null and it is the only column that is allowed to have null values. I am stuck because I don't know how to include all of the columns in the WHERE.

SELECT *
FROM Analytics
WHERE * IS NULL

或者，我可以对一列进行计数，但该表大约有 67 列.

Alternatively, I can do a count for one column, but the table has about 67 columns.

SELECT COUNT(*)
FROM Analytics
WHERE P_Id IS NULL

推荐答案

在 SQL Server 中，您可以借鉴 this answer

In SQL Server you can borrow the idea from this answer

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns)
SELECT *
FROM   Analytics
WHERE  (SELECT Analytics.*
        FOR xml path('row'), elements xsinil, type
        ).value('count(//*[local-name() != "colToIgnore"]/@ns:nil)', 'int') > 0

SQL 小提琴

构建一个包含 67 列的查询可能会更有效，但它可以节省一些输入或需要动态 SQL 来生成它.

Likely constructing a query with 67 columns will be more efficient but it saves some typing or need for dynamic SQL to generate it.

这篇关于查找任何列中具有空值的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！