这个查询怎么实现，在线等待!!!!!!!!!!!!!!!!!!!!!!

[huangjianfen]

大家好，我有两个表：T1,T2,两个表的字段是一样的，有7列，但字段A和字段B的数据可能不一样，其余数据一样。
我想查询出表T1里A，B任何一列在表T2中没有的数据。
我是这样写的：
select * from jam_T1
where not exists (select A.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))

首先T1有1000行，
select A.* from T1 ,T2 where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B)
查出300行
但
select * from jam_T1
where not exists (select A.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))
却一行都没有，是怎么回事？

[huangjianfen]

select * from jam_T1
where not exists (select A.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))

改为

select * from jam_T1
where not exists (select T1.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))

[hyrongg]

select * from jam_T1  
    where  not exists  (
          select 1 FROM T2 
          where rtrim(T2.A)=rtrim(T1.A) and  rtrim(T2.B)=rtrim(T1.B)
)

[mugua604]

select * from jam_T1  
    where  not exists  (select A.* from T1 , T2 
                                  where rtrim(T1.A)=rtrim(T2.A) and  rtrim(T1.B)=rtrim(T2.B))  
以上语句应该等于
select * from jam_T1  where 1<>1

 false =not exists  (select A.* from T1 , T2 
                                  where rtrim(T1.A)=rtrim(T2.A) and  rtrim(T1.B)=rtrim(T2.B))  

[mugua604]

select * from jam_T1  
    where  not exists  (select A.* from T1 , T2 
                                  where rtrim(T1.A)=rtrim(T2.A) and  rtrim(T1.B)=rtrim(T2.B))  
以上语句应该等于
select * from jam_T1  where 1<>1

 false =not exists  (select A.* from T1 , T2 
                                  where rtrim(T1.A)=rtrim(T2.A) and  rtrim(T1.B)=rtrim(T2.B))  

[htl258_Tony]

[Quote=引用 1 楼 huangjianfen 的回复:]
select * from jam_T1
where not exists (select A.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))

改为

select * from jam_T1
where not exists (select T1.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))
[/Quote]
子查询要跟 jam_T1 建立关联关系.

[mugua604]

[code=SQL]
select A.* from T1 ,T2 where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B)
--这一步你已经查出有数据存在了

not exists (select A.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))
--这条语句实际上就等于 假了 也可以好比1=0

select * from jam_T1
where not exists (select A.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))
--所以不关jam_T1 里有多少数据
select * from jam_T1 where 1=0 永远都为空
[code]

[htl258_Tony]

select * from jam_T1 t 
    where  not exists  (select T1.* from T1 , T2 
                                  where rtrim(T1.A)=rtrim(T2.A) and  rtrim(T1.B)=rtrim(T2.B) and t.ID=t1.ID) 

要类似这样.

[huangjianfen]

[Quote=引用楼主 huangjianfen 的帖子:]

select * from jam_T1
where not exists (select A.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))
[/Quote]
写错了：
select * from T1
where not exists (select A.* from T1 , T2
where rtrim(T1.A)=rtrim(T2.A) and rtrim(T1.B)=rtrim(T2.B))


是T1不是jam_T1

[等不到来世]

select * from T1  t
where  not exists  (select 1 from T2 where rtrim(A)=rtrim(t.A) and  rtrim(B)=rtrim(t.B)) 

[huangjianfen]

写错了，只有两个表，jam_T1应为T1

[huangjianfen]

[Quote=引用 2 楼 hyrongg 的回复:]
SQL code
select * from jam_T1
where not exists (
select 1 FROM T2
where rtrim(T2.A)=rtrim(T1.A) and rtrim(T2.B)=rtrim(T1.B)
)
[/Quote]

这个也不行

[mugua604]

select * from T1  a
    where  not exists  (select * from T2
                                  where rtrim(A)=rtrim(a.A)) 

[huangjianfen]

[Quote=引用 12 楼 mugua604 的回复:]
SQL code
select * from T1 a
where not exists (select * from T2
where rtrim(A)=rtrim(a.A))
[/Quote]


有两列呀A，B

[mugua604]

select * from T1  a
    where  not exists  (select * from T2
                                  where rtrim(A)=rtrim(a.A) OR rtrim(B)=rtrim(a.B) ) 

[huangjianfen]

[Quote=引用 14 楼 mugua604 的回复:]
SQL code
select * from T1 a
where not exists (select * from T2
where rtrim(A)=rtrim(a.A) OR rtrim(B)=rtrim(a.B) )
[/Quote]
应该为：
select * from T1 a
where not exists (select * from T2
where rtrim(A)=rtrim(a.A) AND rtrim(B)=rtrim(a.B) )

OK了
可是有一点不明
这个怎么不行
select * from T1
where not exists (select T1.* from T1 a ,T2 b
where rtrim(a.A)=rtrim(b.A) AND rtrim(a.B)=rtrim(b.B) )

[等不到来世]

[Quote=引用 15 楼 huangjianfen 的回复:]
可是有一点不明
这个怎么不行
select * from T1
where not exists (select T1.* from T1 a ,T2 b
where rtrim(a.A)=rtrim(b.A) AND rtrim(a.B)=rtrim(b.B) )
[/Quote]
两个T1没任何关联。
要关联起来，使它们指向同一个表，要使用别名。