求:如何查出两个表中不相同的记录?

[AOKIO]

例:
如:tableA
id name age
1 aa 22
2 bb 33
3 cc 42
4 dd 55
5 gg 32

tableB
id name age
1 aa 22
2 bb 33
3 cc 44
4 dd 55
5 ee 66
6 ff 77

希望查出结果:

tableA 的
3 cc 42
5 gg 32
tableB 的
5 ee 66
6 ff 77

十分感谢！！！！

[marco08]

select * from A
where not exists(select 1 from B where A.id=B.id or A.name=B.name or A.age=B.age)

[marco08]

create table A(id int, name varchar(10), age int)
insert A select 1, 'aa', 22
union all select 2, 'bb', 33
union all select 3, 'cc', 42
union all select 4, 'dd', 55
union all select 5, 'gg', 32

create table B(id int, name varchar(10), age int)
insert B select 1, 'aa', 22
union all select 2, 'bb', 33
union all select 3, 'cc', 44
union all select 4, 'dd', 55
union all select 5, 'ee', 66
union all select 6, 'ff' , 77

select * from A
where not exists(select 1 from B where A.id=B.id and A.name=B.name and A.age=B.age)

--result
/*
id name age
----------- ---------- -----------
3 cc 42
5 gg 32

(2 row(s) affected)
*/

select * from B
where not exists(select 1 from A where A.id=B.id and A.name=B.name and A.age=B.age)
--result
/*
id name age
----------- ---------- -----------
3 cc 44
5 ee 66
6 ff 77

(3 row(s) affected)
*/

[AOKIO]

这样不对吧.我要的是查询到两个表中的记录.谢谢你.

[AOKIO]

select 1 from A where A.id=B.id and A.name=B.name and A.age=B.age
那个 "1" 是什么意思呀?不明白.谢了~

[marco08]

create table A(id int, name varchar(10), age int)
insert A select 1, 'aa', 22
union all select 2, 'bb', 33
union all select 3, 'cc', 42
union all select 4, 'dd', 55
union all select 5, 'gg', 32

create table B(id int, name varchar(10), age int)
insert B select 1, 'aa', 22
union all select 2, 'bb', 33
union all select 3, 'cc', 44
union all select 4, 'dd', 55
union all select 5, 'ee', 66
union all select 6, 'ff' , 77

select id, name, age
from
(
select id, name, age from A
union all
select id, name, age from B
)tmp
group by id, name, age
having count(*)=1

--result
id name age
----------- ---------- -----------
3 cc 42
3 cc 44
5 ee 66
5 gg 32
6 ff 77

(5 row(s) affected)

[baoshan]

1 是一个辅助性的查询用法，如果后面的条件成立的话，就返回一个1或一列1 ，通常结合exists 用比较方便

[AOKIO]

能不能这样呀?比如说,A中有个CC,B中有个CC但是它们age不相同而我只想要一个CC就行.不管后面值改没改变就要那个改变的name应该怎么做呢?