我需要显示包含除第3列之外的所有列的空值的记录。 [英] I need to display the records which contains all columns has null values except the first 3 column.
问题描述
Id name政策费用工资标志.......排名
1 ABC 33. 100 null Null Null
2 DEF 45 Null Null Null。 Null
3 GHI 47 Null 50 Null Null
4 JKL 32 Null。 Null Null。 Null
我的目标应该是
Id名称政策费用工资标志.....排名
2 DEF 45 Null。空值。 Null Null
3 JKL 32 Null。 Null Null。 Null
考虑我的源表包含20k记录,这里有30列我需要查看目标,其中所有列都为空,除了前3列
我尝试过的事情:
我试过为所有人写的不是null 27列名称,这个场景还有其他快捷方式吗?
Id name Policy cost salary flag .......rank
1 ABC 33. 100 null Null Null
2 DEF 45 Null Null Null. Null
3 GHI 47 Null 50 Null Null
4 JKL 32 Null. Null Null. Null
My target should be
Id name policy cost salary flag ..... rank
2 DEF 45 Null. Null. Null Null
3 JKL 32 Null. Null Null. Null
Consider my source table contains 20k records and it has 30 columns here I need to get view of target where all the columns are null except the first 3 columns
What I have tried:
I have tried writing not null for all 27 the column names, is there any other shortcut for this scenario ?
推荐答案
尝试:
Try:
SELECT ... FROM MyTable
WHERE COALESCE(col4, col5, col6, col7, ...) IS NULL
虽然您可以使用COALESCE
来查找第一个非空值,我实际上看到IS NULL没有问题。例如,如果您需要动态设置条件,使用IS NULL
结构可能会更容易。
使用IS NULL
查询类似于
While you can useCOALESCE
to find the first non null value, I actually see no problem with IS NULL. For example if you need to set the conditions dynamically, usingIS NULL
structure may be easier.
UsingIS NULL
the query would be something like
SELECT Id, name, Policy, cost, salary, flag, ..., rank
FROM TableName
WHERE cost IS NULL
AND salary IS NULL
AND flag IS NULL
...
AND rank IS NULL
但是,如果这个条件设置是你经常需要的,那么你可以考虑在您的表中添加计算字段。例如,你可以有一个名为 AllItemsNull
的字段,它可能类似于
However, if this condition set is something you often need, then you could consider adding a calculated field to your table. For example you could have a field named AllItemsNull
which could be something like
ALTER TABLE TableName
ADD AllItemsNull AS CASE
WHEN COALESCE(cost, salary, flag, ..., rank) IS NULL THEN 1
ELSE 0
END
现在检查所有项是否为空的查询可以是
Now the query for checking if all items are null could be
SELECT Id, name, Policy, cost, salary, flag, ..., rank
FROM TableName
WHERE AllItemsNull = 1
附加:
链接以了解有关计算列的更多信息:
在表中指定计算列Microsoft Docs [ ^ ]
这篇关于我需要显示包含除第3列之外的所有列的空值的记录。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!