Question

SQL中什么统计一张表中各个字段不为空值的条数？

例：数据库表内容
SERIALNO ObjectNo ObjectType RelativeSum
0001 0002 null null
null 0003 0004 null
null 0007 0005 null

想得到的结果是
null
SERIALNO 2
ObjectNo 0
ObjectType 1
RelativeSum 3
如果上百个字段改什么处理？

Answer 1

declare @tableName nvarchar(20)
declare @colCount int
declare @colName nvarchar(100)
declare @i int
declare @sql nvarchar(500)

set @i=1
set @tableName='try_decimal'
drop table #a
create table #a
(
colCount int,
name nvarchar(100)
)

select @colCount=count(1) from syscolumns Where ID=OBJECT_ID(@tableName)
print @colCount
while @i<=@colCount
begin
select @colName=A.name from (select ROW_NUMBER() OVER(PARTITION BY ID ORDER BY name) rowNo,name from syscolumns Where ID=OBJECT_ID(@tableName) ) A where A.rowNo=@i
set @sql='insert into #a select count(1),'''+@colName+''' from '+@tableName+' where '+@colName+' is null'
print @sql
exec(@sql)
set @i=@i+1
end
select * from #a

Answer 2

select count(字段) from table

追问

这个我知道，如果一张表上百个字段你什么办

追答

100个字段也得这么写，谁提的这么奇葩的需求让谁写呗。