SQL 查找任何列中具有空值的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14488859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find All Rows With Null Value(s) in Any Column
提问by Snake_Plissken
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 列之外的所有行都具有空值的所有行。某些行在某处将有多个空条目。我想排除一列，因为此时所有条目都为空，并且它是唯一允许具有空值的列。我被卡住了，因为我不知道如何在 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
采纳答案by Martin Smith
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
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 来生成它。
回答by sgeddes
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，我认为您唯一的选择（而不是明确说WHERE col1 IS NULL and col2 IS NULL and col3 IS 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:
您可以使用 FOR 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.
祝你好运。
回答by Kaf
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 + ')')
