--只會這一種復雜的方法 select *,(case when isnull(bus1,'')='' then 0 else 1 end) +(case when isnull(bus2,'')='' then 0 else 1 end) +(case when isnull(bus3,'')='' then 0 else 1 end) +(case when isnull(bus4,'')='' then 0 else 1 end) +(case when isnull(bus5,'')='' then 0 else 1 end) +(case when isnull(bus6,'')='' then 0 else 1 end) 总数 from 你的表;
--创建测试数据: go if OBJECT_ID('tbl')is not null drop table tbl go create table tbl( col1 varchar(5), col2 varchar(5), col3 varchar(5), col4 varchar(5), col5 varchar(5), col6 varchar(5), col7 varchar(5), col8 varchar(5) )
insert tbl select '1',null,'2',null,'3','4','5','6' union all select '1','2','3',null,null,null,null,'4' union all select '1','2',null,'3','4','5','6','7' union all select '1',null,null,'2','3',null,'4',null --静态实现方法 --"+"想加统计各个“case when else”的结果来达到目的: select *, case when col1 is not null then 1 else 0 end+ case when col2 is not null then 1 else 0 end+ case when col3 is not null then 1 else 0 end+ case when col4 is not null then 1 else 0 end+ case when col5 is not null then 1 else 0 end+ case when col6 is not null then 1 else 0 end+ case when col7 is not null then 1 else 0 end+ case when col8 is null then 1 else 0 end as notnull from tbl
--动态实现方法 declare @str varchar(max) set @str='' select @str= @str+'+ case when ' + name +' is not null then 1 else 0 end'+char(13)+char(10)
--char(13)+char(10)实现换行 from syscolumns where id = OBJECT_ID('tbl') --print @str set @str='select *,'+@str+' as notnull from tbl' --print @str exec(@str)
--只會這一種復雜的方法
select
*,(case when isnull(bus1,'')='' then 0 else 1 end)
+(case when isnull(bus2,'')='' then 0 else 1 end)
+(case when isnull(bus3,'')='' then 0 else 1 end)
+(case when isnull(bus4,'')='' then 0 else 1 end)
+(case when isnull(bus5,'')='' then 0 else 1 end)
+(case when isnull(bus6,'')='' then 0 else 1 end) 总数
from 你的表;
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
col1 varchar(5),
col2 varchar(5),
col3 varchar(5),
col4 varchar(5),
col5 varchar(5),
col6 varchar(5),
col7 varchar(5),
col8 varchar(5)
)
insert tbl
select '1',null,'2',null,'3','4','5','6' union all
select '1','2','3',null,null,null,null,'4' union all
select '1','2',null,'3','4','5','6','7' union all
select '1',null,null,'2','3',null,'4',null
--静态实现方法
--"+"想加统计各个“case when else”的结果来达到目的:
select *,
case when col1 is not null then 1 else 0 end+
case when col2 is not null then 1 else 0 end+
case when col3 is not null then 1 else 0 end+
case when col4 is not null then 1 else 0 end+
case when col5 is not null then 1 else 0 end+
case when col6 is not null then 1 else 0 end+
case when col7 is not null then 1 else 0 end+
case when col8 is null then 1 else 0 end as notnull
from tbl
--动态实现方法
declare @str varchar(max)
set @str=''
select @str= @str+'+ case when ' + name +' is not null then 1 else 0 end'+char(13)+char(10)
--char(13)+char(10)实现换行
from syscolumns where id = OBJECT_ID('tbl')
--print @str
set @str='select *,'+@str+' as notnull from tbl'
--print @str
exec(@str)
/*
j结果:
col1 col2 col3 col4 col5 col6 col7 col8 notnull
1 NULL 2 NULL 3 4 5 6 5
1 2 3 NULL NULL NULL NULL 4 3
1 2 NULL 3 4 5 6 7 6
1 NULL NULL 2 3 NULL 4 NULL 5
*/
献丑了