sql server 数据库去重
sql server 数据库去重
如果一条信息完全被另外一条覆盖，则删除该信息(ID除外,ID可取其中任何一个)
idnamesexbirthother
1AAA11970-1-1 SSSS
2AAA1970-1-1 SSSS
31SSSS
4
5BBB11970-1-2
6BBB1970-1-2 CCCC
7BBB1
结果：
1AAA11970-1-1 SSSS
5BBB11970-1-2
6BBB1970-1-2 CCCC
[解决办法]
------------------------------ 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 <X64> (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 allselect 2,'AAA',null,'1970-1-1','SSSS' union allselect 3,null,'1',null,'SSSS' union allselect 4,null,null,null,null union allselect 5,'BBB','1','1970-1-2',null union allselect 6,'BBB',null,'1970-1-2','CCCC' union allselect 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<t.id)select * from tb ----------------结果----------------------------/* id name sex birth other----------- ---- -------- -------- -----1 AAA 1 1970-1-1 SSSS5 BBB 1 1970-1-2 NULL6 BBB NULL 1970-1-2 CCCC(3 行受影响)*/