select b.* from b where not exists(select 1 from a where a=b.a and b=b.b and c=a.c)
declare @a table (a int,b int,c int) insert into @a select 1,2,3 union all select 3,4,5 declare @b table (a int,b int,c int) insert into @b select 1,1,3 union all select 1,2,3 union all select 3,8,1 union all select 3,4,5 select b.* from @b b where not exists (select * from @a a where a.b=b.b)
借用楼上的数据 declare @a table (a int,b int,c int) insert into @a select 1,2,3 union all select 3,4,5 declare @b table (a int,b int,c int) insert into @b select 1,1,3 union all select 1,2,3 union all select 3,8,1 union all select 3,4,5select a,b,c from ( select * from @a union all select * from @b ) t group by a,b,c having count(*)<2 /* a b c ----------- ----------- ----------- 1 1 3 3 8 1 */
if object_id('test1') is not null drop table test1 if object_id('test2') is not null drop table test2 go create table test1(a int,b int ,c int) insert into test1 select 1,2,3 union select 3,4,5 create table test2(a int,b int,c int) insert into test2 select 1,1,3 union select 1,2,3 union select 3,8,1 union select 3,4,5 select * from test2 b where not exists(select 1 from test1 a where a.a=b.a and a.b=b.b and a.c=b.c)(所影响的行数为 2 行) (所影响的行数为 4 行)a b c ----------- ----------- ----------- 1 1 3 3 8 1(所影响的行数为 2 行)
SQL codeselect b.* from b where exists(select 1 from a where a<>b.a or b<>b.b or c<>a.c)
借用楼上的数据declare @a table (a int,b int,c int) insert into @a select 1,2,3 union all select 3,4,5declare @b table (a int,b int,c int) insert into @b select 1,1,3 union all select 1,2,3 union all select 3,8,1 union all select 3,4,5select * from @b EXCEPT select * from @a
declare @a table (a int,b int,c int)
insert into @a select 1,2,3
union all select 3,4,5
declare @b table (a int,b int,c int)
insert into @b select 1,1,3
union all select 1,2,3
union all select 3,8,1
union all select 3,4,5
select b.* from @b b where not exists (select * from @a a where a.b=b.b)
declare @a table (a int,b int,c int)
insert into @a select 1,2,3
union all select 3,4,5
declare @b table (a int,b int,c int)
insert into @b select 1,1,3
union all select 1,2,3
union all select 3,8,1
union all select 3,4,5select a,b,c
from (
select * from @a
union all
select * from @b
) t
group by a,b,c
having count(*)<2
/*
a b c
----------- ----------- -----------
1 1 3
3 8 1
*/
if object_id('test1') is not null
drop table test1
if object_id('test2') is not null
drop table test2
go
create table test1(a int,b int ,c int)
insert into test1 select 1,2,3
union select 3,4,5
create table test2(a int,b int,c int)
insert into test2 select 1,1,3
union select 1,2,3
union select 3,8,1
union select 3,4,5
select * from test2 b where not exists(select 1 from test1 a where a.a=b.a and a.b=b.b and a.c=b.c)(所影响的行数为 2 行)
(所影响的行数为 4 行)a b c
----------- ----------- -----------
1 1 3
3 8 1(所影响的行数为 2 行)
SQL codeselect b.* from b where exists(select 1 from a where a<>b.a or b<>b.b or c<>a.c)
insert into @a select 1,2,3
union all select 3,4,5declare @b table (a int,b int,c int)
insert into @b select 1,1,3
union all select 1,2,3
union all select 3,8,1
union all select 3,4,5select * from @b
EXCEPT
select * from @a
服务器: 消息 156,级别 15,状态 1,行 12
在关键字 'EXCEPT' 附近有语法错误。