34,575
社区成员
发帖
与我相关
我的任务
分享
a
a b c
1 2
4 5 6
create table tbA
(col1 int,col2 int,col3 int,col4 int)
insert tbA
select 1,2,3,null union all
select 1,2,3,null union all
select 1,2,null,null union all
select 1,2,3,null union all
select 1,2,3,null union all
select 1,2,3,null
go
--col1和col2没有空值
declare @sql as nvarchar(4000)
set @sql=''
select @sql=@sql+'+case when not exists(select 1 from tbA where '+[name]+' is null) then '',' +[name]+''' else '''' end'
from sys.columns where [object_id]=object_id('tbA')
set @sql=' declare @sql2 as nvarchar(4000)'+
' set @sql2= '+stuff(@sql,1,1,'')+
' set @sql2=stuff(@sql2,1,1,'''') '+
' exec(''select ''+ @sql2+'' from tbA'')'
exec(@sql)
col1 col2
----------- -----------
1 2
1 2
1 2
1 2
1 2
1 2
(6 row(s) affected)
go
--col1、col2、col3不全为空
declare @sql as nvarchar(4000)
set @sql=''
select @sql=@sql+'+case when exists(select 1 from tbA where '+[name]+' is not null) then '',' +[name]+''' else '''' end'
from sys.columns where [object_id]=object_id('tbA')
set @sql=' declare @sql2 as nvarchar(4000)'+
' set @sql2= '+stuff(@sql,1,1,'')+
' set @sql2=stuff(@sql2,1,1,'''') '+
' exec(''select ''+ @sql2+'' from tbA'')'
exec(@sql)
col1 col2 col3
----------- ----------- -----------
1 2 3
1 2 3
1 2 NULL
1 2 3
1 2 3
1 2 3
(6 row(s) affected)
select * from T WHERE 列1 NOT is null and 列2 not is null
--显示非null列
create table a(a int,b int,c int)
insert a
select 1,2,NULL union all
select 4,5,6
declare @sql nvarchar(4000)
declare @var nvarchar(4000)
select @sql=isnull(@sql+'+','')+'case when not exists(select 1 from a where '+[name]+' is null) then '
+quotename(','+name,'''')+' else '''' end '
from syscolumns where id=object_id('a')
set @sql='select @a='+@sql+' from a '
exec sp_executesql @sql,N'@a varchar(4000) output',@var output
set @sql='select '+stuff(@var,1,1,'')+' from a'
exec(@sql)
drop table a
/*
a b
----------- -----------
1 2
4 5
*/
--或者是
--显示非null列
create table a(a int,b int,c int)
insert a
select 1,2,NULL union all
select 4,5,6
declare @sql nvarchar(4000)
declare @var nvarchar(4000)
select @sql=isnull(@sql+'+','')+'case when exists(select 1 from a where '+[name]+' is not null) then '
+quotename(','+name,'''')+' else '''' end '
from syscolumns where id=object_id('a')
set @sql='select @a='+@sql+' from a '
exec sp_executesql @sql,N'@a varchar(4000) output',@var output
set @sql='select '+stuff(@var,1,1,'')+' from a'
exec(@sql)
drop table a
/*
a b c
----------- ----------- -----------
1 2 NULL
4 5 6
*/
--显示非零列
--考勤奖全为null则不显示
create table a(a int,b int,c int)
insert a
select 1,2,NULL
declare @sql nvarchar(4000)
declare @var nvarchar(4000)
select @sql=isnull(@sql+'+','')+'case when exists(select 1 from a where '+[name]+' is not null) then '
+quotename(','+name,'''')+' else '''' end '
from syscolumns where id=object_id('a')
set @sql='select @a='+@sql+' from a '
exec sp_executesql @sql,N'@a varchar(4000) output',@var output
set @sql='select '+stuff(@var,1,1,'')+' from a'
exec(@sql)
drop table a
/*
a b
----------- -----------
1 2
*/