select * from a where not exists (select * from b) 为什么这样不行呢? 楼上的select 1是什么意思?
--测试环境 declare @t table(a int, b int ,c int) insert into @t select 1,2,3 union all select 2,3,4declare @t1 table(a1 int, b1 int ,c1 int) insert into @t1 select 1,2,3 --查询 select * from @t where exists (select 1 from @t1 where a<>a1 and b<>b1 and c<>c1)--结果a b c ----------- ----------- ----------- 2 3 4(所影响的行数为 1 行)
if exists (select * from dbo.sysobjects where id = object_id(N'[tableA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [tableA] GOCreate table tableA(s1 int,s2 int,s3 int) insert into tableA select 1,3,5 union all select 2,3,7 union all select 4,7,9 union all select 5,6,9if exists (select * from dbo.sysobjects where id = object_id(N'[tableB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [tableB] GO Create table tableB(p1 int,p2 int,p3 int) insert into tableB select 1,3,5 union all select 2,3,7 union all select 4,7,9 select * from tableA select * from tableB select * from tableA a where exists(select 1 from tableB where p1<>a.s1 and p2<>a.s2 and p3<>a.s3) --inner join tableB b on a.s1<>b.p1 and a.s2<>b.p2 and a.s3<>b.p3 drop table tableA drop table tableB
--测试环境 declare @t table(a int, b int ,c int) insert into @t select 1,3,5 union all select 2,3,7 union all select 4,7,9 union all select 5,6,9declare @t1 table(a1 int, b1 int ,c1 int) insert into @t1 select 1,3,5 union all select 2,3,7 union all select 4,7,9 --查询 select * from @t where not exists ( select 1 from @t1 where cast(a as varchar)+'-'+cast(b as varchar)+'-'+cast(c as varchar)= cast(a1 as varchar)+'-'+cast(b1 as varchar)+'-'+cast(c1 as varchar) )--结果 a b c ----------- ----------- ----------- 5 6 9(所影响的行数为 1 行)
又一个方法: --测试环境 declare @t table(a int, b int ,c int) insert into @t select 1,2,3 union all select 2,3,4declare @t1 table(a1 int, b1 int ,c1 int) insert into @t1 select 1,2,3 --查询 select * from @t where not exists( select * from @t1 where a=a1 and b=b1 and c=c1) --结果a b c ----------- ----------- ----------- 2 3 4(所影响的行数为 1 行)
declare @tb1 table(a int, b int ,c int) insert into @tb1 select 1,2,3 union all select 2,3,4 union all select 3,4,5 union all select 4,5,6declare @tb2 table(a1 int, b1 int ,c1 int) insert into @tb2 select 1,2,3 union all select 2,3,4 union all select 3,4,5select * from @tb1 where not exists(select 1 from @tb2 where a=a1 and b=b1 and c=c1)\ /* 测试结果 a b c ----------- ----------- ----------- 4 5 6 */
为什么这样不行呢?
楼上的select 1是什么意思?
declare @t table(a int, b int ,c int)
insert into @t select 1,2,3
union all select 2,3,4declare @t1 table(a1 int, b1 int ,c1 int)
insert into @t1 select 1,2,3
--查询
select * from @t
where exists
(select 1 from @t1 where a<>a1 and b<>b1 and c<>c1)--结果a b c
----------- ----------- -----------
2 3 4(所影响的行数为 1 行)
drop table [tableA]
GOCreate table tableA(s1 int,s2 int,s3 int)
insert into tableA
select 1,3,5
union all
select 2,3,7
union all
select 4,7,9
union all
select 5,6,9if exists (select * from dbo.sysobjects where id = object_id(N'[tableB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tableB]
GO
Create table tableB(p1 int,p2 int,p3 int)
insert into tableB
select 1,3,5
union all
select 2,3,7
union all
select 4,7,9
select * from tableA
select * from tableB
select * from tableA a where exists(select 1 from tableB where p1<>a.s1 and p2<>a.s2 and p3<>a.s3)
--inner join tableB b on a.s1<>b.p1 and a.s2<>b.p2 and a.s3<>b.p3
drop table tableA
drop table tableB
1 3 5
2 3 7
4 7 9
5 6 9
tableB:1 3 5
2 3 7
4 7 9
结果:
1 3 5
2 3 7
4 7 9
5 6 9
declare @t table(a int, b int ,c int)
insert into @t select 1,3,5
union all select 2,3,7
union all select 4,7,9
union all select 5,6,9declare @t1 table(a1 int, b1 int ,c1 int)
insert into @t1 select 1,3,5
union all select 2,3,7
union all select 4,7,9
--查询
select * from @t
where not exists
(
select 1 from @t1 where
cast(a as varchar)+'-'+cast(b as varchar)+'-'+cast(c as varchar)=
cast(a1 as varchar)+'-'+cast(b1 as varchar)+'-'+cast(c1 as varchar)
)--结果
a b c
----------- ----------- -----------
5 6 9(所影响的行数为 1 行)
--测试环境
declare @t table(a int, b int ,c int)
insert into @t select 1,2,3
union all select 2,3,4declare @t1 table(a1 int, b1 int ,c1 int)
insert into @t1 select 1,2,3
--查询
select * from @t where not exists(
select * from @t1 where a=a1 and b=b1 and c=c1)
--结果a b c
----------- ----------- -----------
2 3 4(所影响的行数为 1 行)
insert into @tb1 select 1,2,3
union all select 2,3,4
union all select 3,4,5
union all select 4,5,6declare @tb2 table(a1 int, b1 int ,c1 int)
insert into @tb2 select 1,2,3
union all select 2,3,4
union all select 3,4,5select * from @tb1 where not exists(select 1 from @tb2 where a=a1 and b=b1 and c=c1)\
/*
测试结果
a b c
----------- ----------- -----------
4 5 6
*/