方法一、
select * from (
select id,t1,(case when t1!=0 then 1 else 0 end)+
(case when t2!=0 then 1 else 0 end)+
(case when t3!=0 then 1 else 0 end)+
(case when t4!=0 then 1 else 0 end)+
(case when t5!=0 then 1 else 0 end) as tcount
from tb1
) as mytab where tcount>=1
方法二、
declare @c nvarchar(4000)
select @c=isnull(@c+'+','')+'abs(sign('+a.name +'))'
from syscolumns a inner join sysobjects b on a.id=b.id
where b.name='tb1' and b.type='U' and a.name!='id'
exec ('select id,t1,'+@c+'notZ from tb1')
不能有null值
select * from (
select id,t1,(case when t1!=0 then 1 else 0 end)+
(case when t2!=0 then 1 else 0 end)+
(case when t3!=0 then 1 else 0 end)+
(case when t4!=0 then 1 else 0 end)+
(case when t5!=0 then 1 else 0 end) as tcount
) as mytab where tcount>=1
方法二、
declare @c nvarchar(4000)
select @c=isnull(@c+'+','')+'abs(sign('+a.name +'))'
from syscolumns a inner join sysobjects b on a.id=b.id
where b.name='tb1' and b.type='U' and a.name!='id'
exec ('select id,t1,'+@c+'notZ from tb1')
不能有null值