--显示非null列 create table a(a int,b int,c int) insert a select 1,2,1 union all select null,2,2 union all select null,null,3declare @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/* c ----------- 1 2 3 */
哥哥.其实还是刚才的问题,刚才你给我写的SQL 出来了三个id 但有的是空的 select * from a full join b on a.id=b.id full join c on a.id=c.id我想取出最全的那个ID 有没有简单的办法啊 ?
create table tb(列A int,列B int,列C int) insert into tb select 1, 2, 1 insert into tb select null, 2, 2 insert into tb select null, null, 3 go ;with cte as( select sum(case when 列A is null then 0 else 1 end)a, sum(case when 列B is null then 0 else 1 end)b, sum(case when 列C is null then 0 else 1 end)c from tb )select (case when exists(select 1 from cte where a>b and a>c) then 列A when exists(select 1 from cte where b>a and b>c) then 列B else 列C end) 行最多的列 from tb /* 行最多的列 ----------- 1 2 3(3 行受影响) */ go drop table tb
select isnull(isnull(a.id,b.id),c.id)id from a full join b on a.id=b.id full join c on a.id=c.id --其他字段自己写一下
我就是这么写的 但是结果是不对的 A b c 结果 1 1 1 1 2 2 1 2 NULL NULL 2 2
--结合你前面的那个表 select isnull(isnull(a.id,b.id),c.id)id ,字段A,字段B,字段C, ... 字段K from a full join b on a.id=b.id full join c on a.id=c.id
create table a(a int,b int,c int)
insert a
select 1,2,1 union all
select null,2,2 union all
select null,null,3declare @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/*
c
-----------
1
2
3
*/
出来了三个id 但有的是空的 select * from a full join b
on a.id=b.id
full join c
on a.id=c.id我想取出最全的那个ID 有没有简单的办法啊 ?
insert into tb select 1, 2, 1
insert into tb select null, 2, 2
insert into tb select null, null, 3
go
;with cte as(
select sum(case when 列A is null then 0 else 1 end)a,
sum(case when 列B is null then 0 else 1 end)b,
sum(case when 列C is null then 0 else 1 end)c
from tb
)select (case when exists(select 1 from cte where a>b and a>c) then 列A
when exists(select 1 from cte where b>a and b>c) then 列B
else 列C end) 行最多的列
from tb
/*
行最多的列
-----------
1
2
3(3 行受影响)
*/
go
drop table tb
on a.id=b.id
full join c
on a.id=c.id
--其他字段自己写一下
A b c 结果
1 1 1 1
2 2 1 2
NULL NULL 2 2
select isnull(isnull(a.id,b.id),c.id)id ,字段A,字段B,字段C, ... 字段K
from a full join b
on a.id=b.id
full join c
on a.id=c.id
2 NULL 2 2
3 NULL NULL 3
4 NULL 2 NULL
--如果有两列值一样多怎么办?