SQL SQL查询以检查表中的40列是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/780337/
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
SQL Query to check if 40 columns in table is null
提问by Jhonny D. Cano -Leftware-
How do I select few columns in a table that only contain NULL values for all the rows? Suppose if Table has 100 columns, among this 100 columns 60 columns has null values. How can I write where condition to check if 60 columns are null.
如何在表中选择仅包含所有行的 NULL 值的几列？假设表有 100 列，在这 100 列中，有 60 列有空值。如何编写 where 条件来检查 60 列是否为空。
回答by Jhonny D. Cano -Leftware-
maybe with a COALESCE
也许有一个 COALESCE
SELECT * FROM table WHERE coalesce(col1, col2, col3, ..., colN) IS NULL
回答by Erich Kitzmueller
where c1 is null and c2 is null ... and c60 is null
shortcut using string concatenation(Oracle syntax):
快捷方式使用string concatenation（Oracle 语法）：
where c1||c2||c3 ... c59||c60 is null
回答by John Vasileff
Are you trying to find out if a specific set of 60 columns are null, or do you just want to find out if any 60 out of the 100 columns are null (not necessarily the same 60 for each row?)
您是想找出一组特定的 60 列是否为空，还是只想找出 100 列中是否有 60 列为空（每行不一定是相同的 60？）
If it is the latter, one way to do it in oracle would be to use the nvl2 function, like so:
如果是后者，在 oracle 中执行此操作的一种方法是使用 nvl2 函数，如下所示：
select ... where (nvl2(col1,0,1)+nvl2(col2,0,1)+...+nvl2(col100,0,1) > 59)
A quick test of this idea:
对这个想法的快速测试：
select 'dummy' from dual where nvl2('somevalue',0,1) + nvl2(null,0,1) > 1
Returns 0 rows while:
返回 0 行，同时：
select 'dummy' from dual where nvl2(null,0,1) + nvl2(null,0,1) > 1
Returns 1 row as expected since more than one of the columns are null.
由于不止一列为空，因此按预期返回 1 行。
回答by Filip Skakun
First of all, if you have a table that has so many nulls and you use SQL Server 2008 - you might want to define the table using sparse columns (http://msdn.microsoft.com/en-us/library/cc280604.aspx).
首先，如果您有一个包含如此多空值的表并且您使用 SQL Server 2008 - 您可能想要使用稀疏列 ( http://msdn.microsoft.com/en-us/library/cc280604. aspx）。
Secondly I am not sure if coalesce solves the question asks - it seems like Ammu might actually want to find the list of columns that are null for all rows, but I might have misunderstood. Nevertheless - it is an interesting question, so I wrote a procedure to list null columns for any given table:
其次，我不确定合并是否解决了问题 - 似乎 Ammu 可能实际上想要找到所有行都为空的列列表，但我可能误解了。尽管如此 - 这是一个有趣的问题，所以我编写了一个程序来列出任何给定表的空列：
IF (OBJECT_ID(N'PrintNullColumns') IS NOT NULL)
DROP PROC dbo.PrintNullColumns;
go
CREATE PROC dbo.PrintNullColumns(@tablename sysname)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @query nvarchar(max);
DECLARE @column sysname;
DECLARE columns_cursor CURSOR FOR
SELECT c.name
FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = @tablename AND c.is_nullable = 1;
OPEN columns_cursor;
FETCH NEXT FROM columns_cursor INTO @column;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @query = N'
DECLARE @c int
SELECT @c = COUNT(*) FROM ' + @tablename + ' WHERE ' + @column + N' IS NOT NULL
IF (@c = 0)
PRINT (''' + @column + N''');'
EXEC (@query);
FETCH NEXT FROM columns_cursor INTO @column;
END
CLOSE columns_cursor;
DEALLOCATE columns_cursor;
SET NOCOUNT OFF;
RETURN;
END;
go
回答by DxTx
If you don't want to write the columns names, Try can do something like this.
This will show you all the rows when all of the columns values are null except for the columns you specified (IgnoreThisColumn1 & IgnoreThisColumn2).
如果你不想写列名，Try 可以做这样的事情。
除了您指定的列（IgnoreThisColumn1 和 IgnoreThisColumn2）之外，这将显示当所有列值为空时的所有行。
DECLARE @query NVARCHAR(MAX);
SELECT @query = ISNULL(@query+', ','') + [name]
FROM sys.columns
WHERE object_id = OBJECT_ID('yourTableName')
AND [name] != 'IgnoreThisColumn1'
AND [name] != 'IgnoreThisColumn2';
SET @query = N'SELECT * FROM TmpTable WHERE COALESCE('+ @query +') IS NULL';
EXECUTE(@query)
Result
结果
If you don't want rows when all the columns are null except for the columns you specified, you can simply use IS NOT NULLinstead of IS NULL
如果除了您指定的列之外，所有列都为空时您不想要行，您可以简单地使用IS NOT NULL代替IS NULL
SET @query = N'SELECT * FROM TmpTable WHERE COALESCE('+ @query +') IS NOT NULL';
Result
结果
[![Result Two[2]](/static/img/viewimg.png)
[
回答by user2097808
Here is another method that seems to me to be logical as well (use Netezza or TSQL)
这是另一种在我看来也是合乎逻辑的方法（使用 Netezza 或 TSQL）
SELECT KeyColumn, MAX(NVL2(TEST_COLUMN,1,0) AS TEST_COLUMN
FROM TABLE1
GROUP BY KeyColumn
So every TEST_COLUMNthat has MAXvalue of 0 is a column that contains all nulls for the record set. The function NVL2is saying if the column data is not null return a 1, but if it is null then return a 0.
所以每次TEST_COLUMN有MAX0值是包含该记录集的所有空列。该函数NVL2表示如果列数据不为空返回 1，但如果为空则返回 0。
Taking the MAXof that column will reveal if any of the rows are not null. A value of 1 means that there is at least 1 row that has data. Zero (0) means that each row is null.
取该MAX列的 将显示是否有任何行不为空。值为 1 表示至少有 1 行有数据。零 (0) 表示每一行都为空。
回答by James Black
It would help to know which db you are using and perhaps which language or db framework if using one.
如果使用一种语言或数据库框架，这将有助于了解您正在使用的数据库以及可能的语言或数据库框架。
This should work though on any database.
这应该适用于任何数据库。
Something like this would probably be a good stored procedure, since there are no input parameters for it.
像这样的东西可能是一个很好的存储过程，因为它没有输入参数。
select count(*) from table where col1 is null or col2 is null ...
回答by Nutan P
you can use
您可以使用
select NUM_NULLS , COLUMN_NAME from all_tab_cols where table_name = 'ABC' and COLUMN_NAME in ('PQR','XYZ');
