select * from T WHERE 列1 NOT is null and 列2 not is null
不是这个意思,是为空的列根本不出现在select中,而不是select *
where col is not null
表 a a b c 1 2 我现在要的是,c列为空的时候,select中就不提取c列,就是select a,b
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)试着弄了弄。。
如果是 a a b c 1 2 4 5 6 这样呢?c列要列出来吗
--显示非零列 --考勤奖全为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 */
--显示非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*/
select * from T WHERE 列1 is not null and 列2 is not null ,如果是一列值为空,用or ;如果是两列值为空,用and
a b c
1 2 我现在要的是,c列为空的时候,select中就不提取c列,就是select a,b
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)试着弄了弄。。
如果是
a
a b c
1 2
4 5 6
这样呢?c列要列出来吗
--考勤奖全为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
*/
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*/
楼上还是好好学学sql吧,你这句话的意思是不显示列1为空和列2为空的行,跟列显示不显示没有半毛钱关系