select id,name, isnull(a1,0)+isnull(a2,0)+isnull(a3,0)+.....+isnull(a8,0) as '总和' from aa
如果a1,a2的数据可能不为1,则需要用case when select id, [name], (case when a1 is null then 1 else 0 end) + (case when a2 is null then 1 else 0 end) + (case when a3 is null then 1 else 0 end) + (case when a4 is null then 1 else 0 end) + (case when a5 is null then 1 else 0 end) + (case when a6 is null then 1 else 0 end) + (case when a7 is null then 1 else 0 end) + (case when a8 is null then 1 else 0 end) as 数据个数 from 表名
select (case when a1 is null then 1 else 0 end) + (case when a2 is null then 1 else 0 end) + (case when a3 is null then 1 else 0 end) + (case when a4 is null then 1 else 0 end) + (case when a5 is null then 1 else 0 end) + (case when a6 is null then 1 else 0 end) + (case when a7 is null then 1 else 0 end) + (case when a8 is null then 1 else 0 end) as 数据个数 from 表名
select
id,
[name],
(case when a1 is null then 1 else 0 end) +
(case when a2 is null then 1 else 0 end) +
(case when a3 is null then 1 else 0 end) +
(case when a4 is null then 1 else 0 end) +
(case when a5 is null then 1 else 0 end) +
(case when a6 is null then 1 else 0 end) +
(case when a7 is null then 1 else 0 end) +
(case when a8 is null then 1 else 0 end) as 数据个数
from 表名
09 显示器 1 1 1 1
-------------------------------
我要查询的结果是:4 (表示a1到a8字段里有4个数据的)
(case when a1 is null then 1 else 0 end) +
(case when a2 is null then 1 else 0 end) +
(case when a3 is null then 1 else 0 end) +
(case when a4 is null then 1 else 0 end) +
(case when a5 is null then 1 else 0 end) +
(case when a6 is null then 1 else 0 end) +
(case when a7 is null then 1 else 0 end) +
(case when a8 is null then 1 else 0 end) as 数据个数
from 表名