select sum(case when 列1 is null then 0 else 1 end)+ sum(case when 列2 is null then 0 else 1 end)+ sum(case when 列3 is null then 0 else 1 end)+ sum(case when 列4 is null then 0 else 1 end)+ sum(case when 列5 is null then 0 else 1 end) as [不为空值], sum(case when 列1<30 then 1 else 0 end)+ sum(case when 列2<30 then 1 else 0 end)+ sum(case when 列3<30 then 1 else 0 end)+ sum(case when 列4<30 then 1 else 0 end)+ sum(case when 列5<30 then 1 else 0 end)) as [<30], sum(case when 列1>100 then 1 else 0 end)+ sum(case when 列2>100 then 1 else 0 end)+ sum(case when 列3>100 then 1 else 0 end)+ sum(case when 列4>100 then 1 else 0 end)+ sum(case when 列5>100 then 1 else 0 end)) as [>100] from tab
select sum(isnull(列1,1)) a1,sum(isnull(列2,1)) a2,sum(isnull(列3,1)) a3,sum(isnull(列4,1)) a4,sum(isnull(列5,1)) a5 from t1
--这个用于统计每列中不为空的个数if object_id('tb') is not null drop table tb go create table tb(列1 float,列2 float,列3 float,列4 float,列5 float) insert tb select 100.4, 100.4, 10.45, 10.45, 40.45 union all select 100.3, null, null , 1002, 33 select * from tbselect sum(case when 列1 is not null then 1 else 0 end) as 列1数, sum(case when 列2 is not null then 1 else 0 end) as 列2数, sum(case when 列3 is not null then 1 else 0 end) as 列3数, sum(case when 列4 is not null then 1 else 0 end) as 列4数, sum(case when 列5 is not null then 1 else 0 end) as 列5数 from tb
--接着上面用于统计大于100和小于30的个数 select sum(case when isnull(列1,0)<30 then 1 else 0 end) as 列1数, sum(case when isnull(列2,0)<30 then 1 else 0 end) as 列2数, sum(case when isnull(列3,0)<30 then 1 else 0 end) as 列3数, sum(case when isnull(列4,0)<30 then 1 else 0 end) as 列4数, sum(case when isnull(列5,0)<30 then 1 else 0 end) as 列5数 from tbselect sum(case when isnull(列1,0)>100 then 1 else 0 end) as 列1数, sum(case when isnull(列2,0)>100 then 1 else 0 end) as 列2数, sum(case when isnull(列3,0)>100 then 1 else 0 end) as 列3数, sum(case when isnull(列4,0)>100 then 1 else 0 end) as 列4数, sum(case when isnull(列5,0)>100 then 1 else 0 end) as 列5数 from tb
sum(case when 列2 is null then 0 else 1 end)+
sum(case when 列3 is null then 0 else 1 end)+
sum(case when 列4 is null then 0 else 1 end)+
sum(case when 列5 is null then 0 else 1 end)
as [不为空值],
sum(case when 列1<30 then 1 else 0 end)+
sum(case when 列2<30 then 1 else 0 end)+
sum(case when 列3<30 then 1 else 0 end)+
sum(case when 列4<30 then 1 else 0 end)+
sum(case when 列5<30 then 1 else 0 end))
as [<30],
sum(case when 列1>100 then 1 else 0 end)+
sum(case when 列2>100 then 1 else 0 end)+
sum(case when 列3>100 then 1 else 0 end)+
sum(case when 列4>100 then 1 else 0 end)+
sum(case when 列5>100 then 1 else 0 end))
as [>100]
from tab
select sum(isnull(列1,1)) a1,sum(isnull(列2,1)) a2,sum(isnull(列3,1)) a3,sum(isnull(列4,1)) a4,sum(isnull(列5,1)) a5 from t1
drop table tb
go
create table tb(列1 float,列2 float,列3 float,列4 float,列5 float)
insert tb
select 100.4, 100.4, 10.45, 10.45, 40.45 union all
select 100.3, null, null , 1002, 33 select * from tbselect sum(case when 列1 is not null then 1 else 0 end) as 列1数,
sum(case when 列2 is not null then 1 else 0 end) as 列2数,
sum(case when 列3 is not null then 1 else 0 end) as 列3数,
sum(case when 列4 is not null then 1 else 0 end) as 列4数,
sum(case when 列5 is not null then 1 else 0 end) as 列5数
from tb
select sum(case when isnull(列1,0)<30 then 1 else 0 end) as 列1数,
sum(case when isnull(列2,0)<30 then 1 else 0 end) as 列2数,
sum(case when isnull(列3,0)<30 then 1 else 0 end) as 列3数,
sum(case when isnull(列4,0)<30 then 1 else 0 end) as 列4数,
sum(case when isnull(列5,0)<30 then 1 else 0 end) as 列5数
from tbselect sum(case when isnull(列1,0)>100 then 1 else 0 end) as 列1数,
sum(case when isnull(列2,0)>100 then 1 else 0 end) as 列2数,
sum(case when isnull(列3,0)>100 then 1 else 0 end) as 列3数,
sum(case when isnull(列4,0)>100 then 1 else 0 end) as 列4数,
sum(case when isnull(列5,0)>100 then 1 else 0 end) as 列5数
from tb