一个表T,有十个字段f1-f10,找出所有任意一个字段的值不为NULL的记录怎么写啊?select * from t where f1 is not null
or f2 is not null
or f3 is not null
....
有没有别的写法?
如果有一百个字段,上面的句语也太长了
or f2 is not null
or f3 is not null
....
有没有别的写法?
如果有一百个字段,上面的句语也太长了
@id int
)
return bit
as
begin
declare @r bit
set @r=0
if exists (select 1 from t where id=@id
and (f1 is not null or f2 is not null .... or f10 is not null)
)
set @r=1
return @r
end
go--调用
select * from t
where dbo.fn_IsNotNull(id)=1
这是都不为NULL
我要的是其中一个不为NULL就选出来
declare @aa char(8000)
set @aa = ''
select @aa = aa.name+ ' is not null or '+ @aa from
(select A.NAME from syscolumns a ,sysobjects b where a.id=b.id and b.type = 'U'and b.name = 'tablename') aa
set @aa = left(Ltrim(rtrim(''''+@aa+'''')),len(Ltrim(rtrim(''''+@aa+'''')))-2)
set @aa = stuff(ltrim(rtrim(''''+@aa+'''')),1,2,'')
execute('select * from tablename where '+@aa)
group by f1,f2,f3,f4,f5,f6,f7,f8,f9,f10
order by f1,f2,f3,f4,f5,f6,f7,f8,f9,f10此时所有10个字段都为空的记录都合并为一条且在结果集的第一个.所以你可以选择top 1然后排除之就可以了.
这是都不为NULL
我要的是其中一个不为NULL就选出来
----------------------------------
create table test1(a varchar(10),b varchar(10),c varchar(10),d varchar(10))insert into test1 select '1',null,'a','a'
union all select '1','a','a','a'
union all select '1','a','a','a'
union all select '1','a','a','a'
union all select null,'a','a','a'select * from test1 where a+b+c+d is not null
所以楼上的的写法也不满足条件非的或 = 与的非所以楼主的语句可以换成WHERE NOT (f1 IS NULL AND F2 IS NULL AND F3 IS NULL)