SQL code----------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-06 14:29:26
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(3),[sex] varchar(8),[birth] varchar(8),[other] varchar(4))
insert [tb]
select 1,'AAA','1','1970-1-1','SSSS' union all
select 2,'AAA',null,'1970-1-1','SSSS' union all
select 3,null,'1',null,'SSSS' union all
select 4,null,null,null,null union all
select 5,'BBB','1','1970-1-2',null union all
select 6,'BBB',null,'1970-1-2','CCCC' union all
select 7,'BBB','1',null,null
--------------开始查询--------------------------
delete t from tb t where exists(select 1 from tb where (name=t.name or t.name is null) and (birth=t.birth or t.birth is null) and (sex=t.sex or t.sex is null) and (other=t.other or t.other is null) and id
select * from tb
----------------结果----------------------------
/* id name sex birth other
----------- ---- -------- -------- -----
1 AAA 1 1970-1-1 SSSS
5 BBB 1 1970-1-2 NULL
6 BBB NULL 1970-1-2 CCCC
(3 行受影响)
*/