一、创建并插入数据
create
table
Consumers
(
con_name
varchar
(
20
)
not
null,
address
varchar
(
10
)not
null,
con_id
int
not
null,
fam
int
)
insert
into
Consumers
(
con_name
,
address
,
con_id
,
fam
)
values
(
'Bob'
,
'A'
,
1
,null),
(
'Joe'
,
'B'
,
3
,null),
(
'Mark'
,
'C'
,
5
,null),
(
'Mary'
,
'A'
,
2
,
1
),
(
'Vickie'
,
'B'
,
4
,
3
),
(
'Wayne'
,
'D'
,
6
,null)
二、查询语句
1、解惑一
delete
from
Consumers
where
fam
is
null
and
(
select
COUNT
(*)
from
Consumers
as
c2
where
c1
.
address
=
c2
.
address
)
>
1
2、解惑二
delete
from
Consumers
from
Consumers
as
c1
where
fam
is
null
and
exists
(
select
*
from
Consumers
as
c2
where
c1
.
con_id
<>
c2
.
con_id
and
c1
.
address
=
c2
.
address
and
c2
.
fam
is
not
null)
3、解惑三
delete
from
Consumers
from
Consumers
as
c1
where
c1
.
fam
is
null
and
exists
(
select
*
from
Consumers
as
c2
where
c2
.
fam
=
c1
.
con_id
)
三、改进
select
C1
.*
from
Consumers
as
c1
except
select
C1
.*
from
Consumers
as
c1
where
c1
.
con_id
in
(
select
fam
from
Consumers
as
c3
where
c3
.
address
=
c1
.
address
)
由于语句的话只要筛选出需要删除的数据,然后再用except去反选即可。