1 找出返回 #t 但不存在 #t2 的行--2005
select * from #t
except
select * from #t2
/*
name
----
c
*/--2000
select * from #t a where not exists (select * from #t2 b where isnull(a.name,0)=isnull(b.name,0))--2000
select name from
(select px=2,* from #t
union
select px=1,* from #t2)x
group by name
having count(*)=1 and max(px)=2求 except_all的写法
/*
name
------
a
c
*/--2005 with cte
as
(select name,id=row_number() over(partition by name order by name) from #t
except
select name,id=row_number() over(partition by name order by name) from #t2
)
select name from cte;求 2000的写法
select * from #t
except
select * from #t2
/*
name
----
c
*/--2000
select * from #t a where not exists (select * from #t2 b where isnull(a.name,0)=isnull(b.name,0))--2000
select name from
(select px=2,* from #t
union
select px=1,* from #t2)x
group by name
having count(*)=1 and max(px)=2求 except_all的写法
/*
name
------
a
c
*/--2005 with cte
as
(select name,id=row_number() over(partition by name order by name) from #t
except
select name,id=row_number() over(partition by name order by name) from #t2
)
select name from cte;求 2000的写法
select * into #t from (select name='a' union all select 'a' union all select null union all select 'b' union all select 'c')x
#t
/*
name
----
a
a
NULL
b
c
*/
select * into #t2 from (select name=null union all select 'a' union all select 'd' union all select 'b' union all select 'b')x#t1
/*
name
----
NULL
a
d
b
b
*/
from #t a full join #t2 b on a.name=b.name where case when a.name is not null and b.name is null then a.name when a.name is null and b.name is not null then b.name end is not null----
c
d(2 行受影响)
/*
name
------
a
c
*/
/*
name
----
a
a
NULL
b
c
*/
select * into #t2 from (select name=null union all select 'a' union all select 'd' union all select 'b' union all select 'b')x #t1
/*
name
----
NULL
a
d
b
b
*/
select top 100 id=identity(int,1,1) into #id from sysobjectsselect c.name from
(select a.name,b.id from (select name ,con=count(1) from #t group by name) a left join #id b on a.con>=b.id ) c
where not exists
(
select 1 from (select a.name,b.id from (select name ,con=count(1) from #t2 group by name) a left join #id b on a.con>=b.id )d
where isnull(c.name,'')=isnull(d.name,'') and c.id<=isnull(d.id,0)
)drop table #idname
----
a
c(2 行受影响)
如果一个表里的数据是null, 另一个是''
按照你的逻辑岂不是按相等算了?