请大侠们看看，SQL整理重复数据
CustomerID ChName Mobile Address CardID LinkMan LinkMobile CreateDate 120612001 王小二 NULL NULL 000781 NULL NULL 2011-12-3 120612002 XXXX公司 13656778787 Null 000781 NULL 13566677981 2010-9-10 120612003 yyyy公司 Null 上海 000781 王斯 13466677881 NULL 120612004 zzzz公司 13466677881 北京 000781 NULL NULL 2011-9-10 120612005 李雷 Null 上海 000782 张三 13466677882 NULL 120612006 GGGG公司 13466677882 广州 000782 NULL NULL NUll 120612007 张兵 Null 上海 000783 Jim 13466677883 NULL 120612008 张兵 13466677885 NULL 000783 NULL NULL 2010-9-1 120612009 张兵 13466677886 天津 000783 NULL 13866654566 NULL 条件：1.CardID唯一 2.CardID相同记录中，如果其中存在CreateDate不为空，那么保留距今天最近的一条； 如果CardID相同记录中CreateDate都为空则包里Mobile不为空的数据 3.保留的记录中，如果这条记录中字段为空那么取CardID相同记录中其他不为空的数据， 如果其中存在多条不为空的记录，取值也是按CreateDate距今最近的不为空的值, 若CreateDate都为空，则取Mobile不为空的一条中该字段不为空的值 ， 若Mobile都为空，则取CustomerID值最大的那条记录中该字段不为空的值 希望通过sql语句整理后，customerinfo表中保留的结果如下： CustomerID ChName Mobile Address CardID LinkMan LinkMobile CreateDate 120612001 王小二 13466677881 北京 000781 王斯 13566677981 2011-12-3 120612006 GGGG公司 13466677882 广州 000782 张三 13466677882 NUll 120612008 张兵 13466677885 天津 000783 Jim 13866654566 2010-9-1 将其他数据删除，并将删除的数据保存到与customerinfo表结构相同的表tempinfo中(数据有很多，只选取了具有代表性的几条数据)
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([CustomerID] int,[ChName] varchar(8),[Mobile] bigint,[Address] varchar(4),[CardID] varchar(6),[LinkMan] varchar(4),[LinkMobile] bigint,[CreateDate] datetime)insert [tb]select 120612001,'王小二',null,null,'000781',null,null,'2011-12-3' union allselect 120612002,'XXXX公司',13656778787,null,'000781',null,13566677981,'2010-9-10' union allselect 120612003,'yyyy公司',null,'上海','000781','王斯',13466677881,null union allselect 120612004,'zzzz公司',13466677881,'北京','000781',null,null,'2011-9-10' union allselect 120612005,'李雷',null,'上海','000782','张三',13466677882,null union allselect 120612006,'GGGG公司',13466677882,'广州','000782',null,null,null union allselect 120612007,'张兵',null,'上海','000783','Jim',13466677883,null union allselect 120612008,'张兵',13466677885,null,'000783',null,null,'2010-9-1' union allselect 120612009,'张兵',13466677886,'天津','000783',null,13866654566,nullgoselect * from tb twhere CustomerID=(select top 1 CustomerID from tb where CardID=t.CardID order by CreateDate desc, Mobile desc, CustomerID desc) /**CustomerID ChName Mobile Address CardID LinkMan LinkMobile CreateDate----------- -------- -------------------- ------- ------ ------- -------------------- -----------------------120612001 王小二 NULL NULL 000781 NULL NULL 2011-12-03 00:00:00.000120612006 GGGG公司 13466677882 广州 000782 NULL NULL NULL120612008 张兵 13466677885 NULL 000783 NULL NULL 2010-09-01 00:00:00.000(3 行受影响)**/
[解决办法]
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([CustomerID] int,[ChName] varchar(8),[Mobile] bigint,[Address] varchar(4),[CardID] varchar(6),[LinkMan] varchar(4),[LinkMobile] bigint,[CreateDate] datetime)insert [test]select 120612001,'王小二',null,null,'000781',null,null,'2011-12-3' union allselect 120612002,'XXXX公司',13656778787,null,'000781',null,13566677981,'2010-9-10' union allselect 120612003,'yyyy公司',null,'上海','000781','王斯',13466677881,null union allselect 120612004,'zzzz公司',13466677881,'北京','000781',null,null,'2011-9-10' union allselect 120612005,'李雷',null,'上海','000782','张三',13466677882,null union allselect 120612006,'GGGG公司',13466677882,'广州','000782',null,null,null union allselect 120612007,'张兵',null,'上海','000783','Jim',13466677883,null union allselect 120612008,'张兵',13466677885,null,'000783',null,null,'2010-9-1' union allselect 120612009,'张兵',13466677886,'天津','000783',null,13866654566,nullwith tas(select *,px=ROW_NUMBER()over(partition by [CardID] order by [CreateDate] desc,Mobile desc)from test)select CustomerID, ChName, isnull(Mobile,(select Mobile from t b where b.[CustomerID]=(select max([CustomerID]) from t c where c.[CardID]=a.[CardID]))) as Mobile, isnull([address],(select [address] from t b where b.[CustomerID]=(select max([CustomerID]) from t c where c.[CardID]=a.[CardID]))) as [address], CardID, isnull([LinkMan],(select [LinkMan] from t b where b.[CustomerID]=(select max([CustomerID]) from ( select * from t where [LinkMan] is not null ) c where c.[CardID]=a.[CardID]))) as [LinkMan], isnull([LinkMobile],(select [LinkMobile] from t b where b.[CustomerID]=(select max([CustomerID]) from (select * from t where [LinkMobile] is not null) c where c.[CardID]=a.[CardID]))) as [LinkMobile], CreateDatefrom t a where px=1/*CustomerID ChName Mobile address CardID LinkMan LinkMobile CreateDate120612001 王小二 13466677881 北京 000781 王斯 13466677881 2011-12-03 00:00:00.000120612006 GGGG公司 13466677882 广州 000782 张三 13466677882 NULL120612008 张兵 13466677885 天津 000783 Jim 13866654566 2010-09-01 00:00:00.000*/
[解决办法]
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([CustomerID] int,[ChName] varchar(8),[Mobile] bigint,[Address] varchar(4),[CardID] varchar(6),[LinkMan] varchar(4),[LinkMobile] bigint,[CreateDate] datetime)insert [test]select 120612001,'王小二',null,null,'000781',null,null,'2011-12-3' union allselect 120612002,'XXXX公司',13656778787,null,'000781',null,13566677981,'2010-9-10' union allselect 120612003,'yyyy公司',null,'上海','000781','王斯',13466677881,null union allselect 120612004,'zzzz公司',13466677881,'北京','000781',null,null,'2011-9-10' union allselect 120612005,'李雷',null,'上海','000782','张三',13466677882,null union allselect 120612006,'GGGG公司',13466677882,'广州','000782',null,null,null union allselect 120612007,'张兵',null,'上海','000783','Jim',13466677883,null union allselect 120612008,'张兵',13466677885,null,'000783',null,null,'2010-9-1' union allselect 120612009,'张兵',13466677886,'天津','000783',null,13866654566,nullselect * from test with tas(select *,px=ROW_NUMBER()over(partition by [CardID] order by [CreateDate] desc,Mobile desc)from test)select CustomerID, ChName, isnull(Mobile,(select Mobile from t b where b.[CustomerID]=(select max([CustomerID]) from t c where c.[CardID]=a.[CardID]))) as Mobile, isnull([address],(select [address] from t b where b.[CustomerID]=(select max([CustomerID]) from t c where c.[CardID]=a.[CardID]))) as [address], CardID, isnull([LinkMan],(select [LinkMan] from t b where b.[CustomerID]=(select max([CustomerID]) from ( select * from t where [LinkMan] is not null ) c where c.[CardID]=a.[CardID]))) as [LinkMan], isnull([LinkMobile],(select [LinkMobile] from t b where b.[CustomerID]=(select max([CustomerID]) from (select * from t where [LinkMobile] is not null) c where c.[CardID]=a.[CardID]))) as [LinkMobile], CreateDate into #tblfrom t a where px=1--删除数据:select * into tempinfo from test where CustomerID not in (select CustomerID from #tbl)delete from test where CustomerID not in (select CustomerID from #tbl)--更新数据成需要的结果:update test set test.ChName=a.ChName,test.Mobile=a.Mobile,test.[Address]=a.[Address],test.LinkMan=a.LinkMan,test.LinkMobile=a.LinkMobile,test.CreateDate=a.CreateDatefrom #tbl a where a.CustomerID=test.CustomerIDselect * from test /*CustomerID ChName Mobile Address CardID LinkMan LinkMobile CreateDate120612001 王小二 13466677881 北京 000781 王斯 13466677881 2011-12-03 00:00:00.000120612006 GGGG公司 13466677882 广州 000782 张三 13466677882 NULL120612008 张兵 13466677885 天津 000783 Jim 13866654566 2010-09-01 00:00:00.000*/