--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(20),action varchar(20), varchar(20))
insert [tb]
select 1,null,2 union all
select null,1,1 union all
select 1,null,2 union all
select null,null,null
select * from tb如何能去除为null的数据,得到一下结果
id action
1 1 2
1 1
2
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(20),action varchar(20), varchar(20))
insert [tb]
select 1,null,2 union all
select null,1,1 union all
select 1,null,2 union all
select null,null,null
select * from tb如何能去除为null的数据,得到一下结果
id action
1 1 2
1 1
2
ISNULL(LTRIM(action ),'')AS action ,
ISNULL(LTRIM( ),'')AS
FROM TB
select id=isnull(id,''), action=isnull(action,''),=isnull(,'') from tb
id action
-------------------- -------------------- --------------------
1 2
1 1
1 2
(4 行受影响)
ISNULL(LTRIM(action ),'')AS action ,
ISNULL(LTRIM( ),'')AS
FROM TB77好快!
isnull(ltrim(id),'') as id,
isnull(ltrim(action),'') as action,
innull(ltrim(),'') as
from
tb
create table [tb]([id] varchar(20),action varchar(20), varchar(20))
insert [tb]
select 1,null,2 union all
select null,1,1 union all
select 1,null,2 union all
select null,null,null select id , px = identity(int,1,1) into t1 from tb where id is not null
select action , px = identity(int,1,1) into t2 from tb where action is not null
select , px = identity(int,1,1) into t3 from tb where is not nullselect isnull(m.id,'') id ,
isnull(m.action,'') action,
isnull(n.,'')
from
(
select isnull(t1.id,'') id ,isnull(t2.action,'') action , isnull(t1.px , t2.px) px from t1 full join t2 on t1.px = t2.px
) m
full join t3 n
on m.px = n.pxdrop table tb , t1,t2,t3/*
id action
-------------------- -------------------- --------------------
1 1 2
1 1
2(所影响的行数为 3 行)*/
id action
1 2
1 1
1 2
null的地方就成了空的了!