例:
如: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
十分感谢!!!!
...全文
3017打赏收藏
求:如何查出两个表中不相同的记录?
例: 如: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 十分感谢!!!!
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
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