SQL中如何统计一条记录中所有空值字段？

有什么方法可以统计除主键列之外的特定记录的所有具有空值的字段吗？

Example:

ID  Name    Age    City     Zip

1   Alex    32     Miami    NULL
2   NULL    24     NULL     NULL

作为输出，我需要获取 1 和 3。没有显式指定列名称。

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

Result:

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

Update:

这是一个更好的版本。谢谢马丁·史密斯 https://stackoverflow.com/users/73226/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

Update:

并且具有更快一点的 XQuery 表达式。

;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