删除有两个字段相同的多条重复记录，只保存一条

?要求 ：把T_FORM_RECEIPT表中[FORM_NAME_],[OPT_USER_]字段相同的重复记录，重复记录只留 [CREATE_DATE_] 最

???????????? 大的那条记录

表 ： CREATE TABLE [dbo].[T_FORM_RECEIPT](
??????? [ID_] [varchar](32) COLLATE Chinese_PRC_CI_AS NOT NULL,
???????? [FORM_NAME_] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
??????? [OPT_USER_] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
?????? [CREATE_DATE_] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
?????? [CONTENT_] [text] COLLATE Chinese_PRC_CI_AS NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

解决办法：

alert table T_FORM_RECEIPT add flag int? //添加标示列，为1的为有用的

update T_FORM_RECEIPT set flag=1?? from (select max(create_Date_) as Date_,form_name_ as name_,opt_User_ as user_? from T_FORM_RECEIPT? group by form_name_,opt_User_?? having count(*) >1) as c? where create_Date_=c.Date_ and form_name_=c.name_? and? opt_User_=c.user_?? //多条重复中 保留[CREATE_DATE_] 最 大的那条记录

update T_FORM_RECEIPT set flag=1?? from (select min(id_) as id from T_FORM_RECEIPT? group by form_name_,opt_User_ having count(*) =1) as c? where id_ =c.id?? //两字段没有重复的，只有一条的，保留

delete from T_FORM_RECEIPT where flag is null? //删除那些flag不等于1的（在sqlserver 中null 不能由！=1识别出来，所以这么写）

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!