如何在SQL中计算一条记录中具有空值的所有字段? [英] How to count in SQL all fields with null values in one record?

问题描述

有没有办法计算除 PrimaryKey 列之外的特定记录的所有具有空值的字段?

Is there any way to count all fields with null values for specific record excluding PrimaryKey column?

Example:

ID  Name    Age    City     Zip

1   Alex    32     Miami    NULL
2   NULL    24     NULL     NULL

作为输出，我需要得到 1 和 3.没有明确指定列名.

As output I need to get 1 and 3. Without explicitly specifying column names.

推荐答案

declare @T table
(
  ID int,
  Name varchar(10),
  Age int,
  City varchar(10),
  Zip varchar(10)
)  

insert into @T values 
(1, 'Alex', 32, 'Miami', NULL),
(2,  NULL,  24,  NULL,   NULL)

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select *
          from @T as T2
          where T1.ID = T2.ID
          for xml path('row'), elements xsinil, type 
       ).value('count(/row/*[@ns:nil = "true"])', 'int') as NullCount
from @T as T1

结果:

ID          NullCount
----------- -----------
1           1
2           3

更新:

这是一个更好的版本.感谢马丁·史密斯.

Here is a better version. Thanks to Martin Smith.

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select T1.*
          for xml path('row'), elements xsinil, type 
       ).value('count(/row/*[@ns:nil = "true"])', 'int') as NullCount
from @T as T1

更新:

还有更快的 XQuery 表达式.

And with a bit faster XQuery expression.

;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select ID,
       (
          select T1.*
          for xml path('row'), elements xsinil, type 
       ).value('count(//*/@ns:nil)', 'int') as NullCount
from @T as T1

这篇关于如何在SQL中计算一条记录中具有空值的所有字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！