昨天有人在论坛上问到如何查找多余记录,其实并不难:

create database abcde
go
use abcde
go
create table a(
id int primary key,
name char(8),
sex char(2)
)
go
create table c(
id int primary key,
name char(8),
sex char(2)
)
go
create table d(
id int primary key,
name char(8),
sex char(2)
)
go
create table e(
id int primary key,
name char(8),
sex char(2)
)
go

insert into a
values(101,'张三','男')
go
insert into a
values(102,'李四','女')
go
insert into a
values(103,'王五','男')
go

insert into c
values(101,'张三','男')
go
insert into c
values(102,'李四','女')
go
insert into c
values(103,'王五','男')
go
insert into c
values(104,'陈六','女') --多出的
go

insert into d
values(101,'张三','男')
go
insert into d
values(102,'李四','女')
go
insert into d
values(103,'王五','男')
go
insert into d
values(104,'陈六','女') --多出的
go

insert into e
values(101,'张三','男')
go
insert into e
values(102,'李四','女')
go
insert into e
values(103,'王五','男')
go
insert into e
values(104,'陈六','女') --多出的
go

use abcde
go
select b.* from
(select c.* from c,d,e
where (c.id=d.id) and (c.id=e.id) and (d.id=e.id)
) as b
where b.id not in(select id from a)
go
查询结果:
   id     name   sex
-----   --------    ----
104     陈六      女