创建一个查询以检查表中的任何列是否为空 [英] Create a Query to check if any Column in a table is Null

问题描述

我对SQL的经验为零，但正在尝试学习如何验证表.我正在尝试查看表中是否有任何列为空.

I have zero experience with SQL but am trying to learn how to validate tables. I am trying to see within a table if any of the columns are null.

目前，我一直在使用一个只计算空值数量的脚本.我正在为每一列这样做.有没有更好的脚本可用来检查表中的所有列?

Currently I have been going with a script that is just counting the number of nulls. I am doing this for each column. Is there a better script that I can use to check all the columns in a table?

select count(id) from schema.table where id is not null

如果有100条记录，我希望所有列都返回100，但如果一列为空，它将显示0.

If there are 100 records I would expect all columns to come back with 100 but if one column is null it will show a 0.

推荐答案

您可以使用 sum 和 case 来对单个查询中的每一列进行计数:

You can count each column in a single query by using sum and case:

select
  sum(case when Column1 is null then 1 else 0 end) Column1NullCount
  , sum(case when Column2 is null then 1 else 0 end) Column2NullCount
  -- ...
  , sum(case when ColumnN is null then 1 else 0 end) ColumnNNullCount
from MyScheme.MyTable

这篇关于创建一个查询以检查表中的任何列是否为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！