在任何列中查找具有空值的所有行

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.

我正在尝试创建一个查询，它将返回除1列以外所有行都具有null值的所有行。某些行将有多个空项。有一列我要排除，因为此时所有的项都是空的，它是唯一允许有空值的列。我被困住了，因为我不知道如何包含WHERE中的所有列。

SELECT *
FROM Analytics
WHERE * IS NULL

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

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

SELECT COUNT(*)
FROM Analytics
WHERE P_Id IS NULL

3 个解决方案

#1

In SQL Server you can borrow the idea from this answer

在SQL Server中，您可以从这个答案中借用这个想法。

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

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.

构造一个包含67列的查询可能会更有效，但它省去了一些输入，或者需要动态SQL生成查询。

#2

Depending on which RDBMS you're using, I think your only option (rather than explicitly saying WHERE col1 IS NULL and col2 IS NULL and col3 IS NULL ...) would be to use Dynamic SQL.

根据您正在使用的RDBMS，我认为您唯一的选项(而不是明确地说col1是NULL, col2是NULL, col3是NULL…)将是使用动态SQL。

For example, if you want to get all the column names from a SQL Server database, you could use something like this to return those names:

例如，如果您想从SQL Server数据库中获取所有的列名，可以使用以下方法返回这些列名:

SELECT
     name
FROM
     sys.columns
WHERE
     object_id = OBJECT_ID('DB.Schema.Table')

You could use FOR XML to create your WHERE clause:

您可以使用XML创建WHERE子句:

SELECT Name + ' IS NULL AND ' AS [text()]
FROM sys.columns c1
WHERE     object_id = OBJECT_ID('DB.Schema.Table')
ORDER BY Name
FOR XML PATH('')

Hope this helps get you started.

希望这能帮助你开始。

Good luck.

祝你好运。

#3

I don't have such a table to test, assuming there is no 'x' as data in any field, I think this should work on Sql-Server; (DEMO)

我没有这样的表来测试，假设在任何字段中都没有'x'作为数据，我认为这应该在Sql-Server上工作;(演示)

NOTE: I have filtered keyColumn as c.name != 'keyColumn'

注意:我将keyColumn过滤为c.name != 'keyColumn'

DECLARE @S NVARCHAR(max), @Columns VARCHAR(50), @Table VARCHAR(50)

SELECT @Columns = '66', --Number of cols without keyColumn
       @Table = 'myTable'

SELECT @S =  ISNULL(@S+'+ ','') + 'isnull(convert(nvarchar, ' + c.name + '),''x'')'  
FROM sys.all_columns c 
WHERE c.object_id = OBJECT_ID(@Table) AND c.name != 'keyColumn'

exec('select * from '+@Table+' where ' + @S + '= replicate(''x'',' + @Columns + ')')

#1