比如一张表
按ID来统计不为空的字段数
id year a b c d ......
1 2000 NULL 1 2 3
2 2001 5 NULL NULL 5
3 2001 7 8 NULL 6
结果id year 不为空数
1 2000,2001 8
谢谢~
按ID来统计不为空的字段数
id year a b c d ......
1 2000 NULL 1 2 3
2 2001 5 NULL NULL 5
3 2001 7 8 NULL 6
结果id year 不为空数
1 2000,2001 8
谢谢~
declare @t table (id int,[year] int,a int,b int,c int,d int)
insert into @t
select 1,2000,null,1,2,3 union all
select 2,2001,5,null,null,5 union all
select 3,2001,7,8,null,6select
'2000-2001' as [year],
count(a)+count(b)+count(c)+count(d) as 不为空数
from @t
where [year] between 2000 and 2001
/*
year 不为空数
--------- -----------
2000-2001 8
*/
declare @t table (id int,[year] int,a int,b int,c int,d int)
insert into @t
select 1,2000,null,1,2,3 union all
select 2,2001,5,null,null,5 union all
select 3,2001,7,8,null,6select
min(id) as id,
ltrim(min([year]))+'-'+ltrim(max([year])) as [year],
count(a)+count(b)+count(c)+count(d) as 不为空数
from @t /*
id year 不为空数
----------- ------------------------- -----------
1 2000-2001 8
*/
select *,(case when a is not null then 1 else 0 end+
case when b is not null then 1 else 0 end+
case when c is not null then 1 else 0 end+
case when d is not null then 1 else 0 end) as notnullcount
from table
1 2000 NULL 1 2 3
1 2001 5 NULL NULL 5
1 2003 7 8 NULL 6
2 2012 1 NULL NULL NULL
......
结果:id year 不为空数
1 2000,2001,2003 8
2 2012 1
......
谢谢~