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

要求 ：把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识别出来，所以这么写）