删除重复记录建立唯一索引

工作中，因为原数据表设计的问题导致存在重复数据，表中存在部分列数据相同的重复记录，下面记录解决实际解决办法：删除重复记录（保留一条），并用这几列建立唯一索引，从而在数据库避免重复数据的插入。

/*
执行步骤：
1、查询总记录数，重复记录数，重复记录数去重之后的数据，可以得到最终去掉重复数据之后的总记录数
2、将去掉重复记录之后的所有记录保存到临时表
3、删除原表
4、将临时表数据插入到原数据表
5、删除临时表
6、创建唯一索引
*/

–步骤一
– 总记录数
SELECT COUNT(*)
  FROM [TRAINING].[YFSS_LEARN_USER_COURSE]
GO
–重复记录
select count(*) from (select  *  from [TRAINING].[YFSS_LEARN_USER_COURSE] as a
   where ((select COUNT(*) from [TRAINING].[YFSS_LEARN_USER_COURSE]
    where a.COURSE_ID = COURSE_ID and a.USER_ID=USER_ID and a.COURSEWARE_ID =COURSEWARE_ID
    and a.TERMYEAR = TERMYEAR))>1 ) as tab1
GO 
–过滤这些重复记录之后
select count(*) from (select distinct USER_ID,COURSE_ID,COURSEWARE_ID,TERMYEAR  from [TRAINING].[YFSS_LEARN_USER_COURSE] as a
   where ((select COUNT(*) from [TRAINING].[YFSS_LEARN_USER_COURSE]
    where a.COURSE_ID = COURSE_ID and a.USER_ID=USER_ID and a.COURSEWARE_ID =COURSEWARE_ID
    and a.TERMYEAR = TERMYEAR))>1 ) as tab2
 GO   
   
–步骤二
–将结果放入临时表，并进行编号
select identity(int,1,1) as autoID, * into #Tmp from [TRAINING].[YFSS_LEARN_USER_COURSE]
–去掉重复记录保存autoID
select min(autoID) as autoID into #Tmp2 from #Tmp group by USER_ID,COURSE_ID,COURSEWARE_ID,TERMYEAR  –过滤此四列内容相同的数据
GO
– 去掉重复记录之后的结果数
select  COUNT(*) from #Tmp where autoID in(select autoID from #tmp2)
GO

–步骤三–删除原表
–truncate table TRAINING.YFSS_LEARN_USER_COURSE
DROP TABLE TRAINING.YFSS_LEARN_USER_COURSE
GO
–步骤四–将临时表中去重后的结果插入原表
select * into TRAINING.YFSS_LEARN_USER_COURSE
from(
   select  [ID]
      ,[USER_ID],[COURSE_ID],[STUDYSTATUS],[TIME]
      ,[HOURS],[FIRSTTIME],[LASTTIME],[COMPLETETIME]
      ,[ENSURETIME],[STATUS],[DURATION],[DELFLAG],[COURSEWARE_ID]
      ,[TERMYEAR]
      from #Tmp where autoID in(select autoID from #tmp2)
     ) as tab3
GO
–步骤五–删除临时表
drop table #Tmp
drop table #tmp2
GO
–步骤六–创建唯一索引，及其相关主外键、字段默认值等
create unique nonclustered index IX_YFSS_LEARN_USER_COURSE
on [TRAINING].[YFSS_LEARN_USER_COURSE] (USER_ID,COURSE_ID,COURSEWARE_ID,TERMYEAR)
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE]  WITH CHECK ADD
 CONSTRAINT [PK_YFSS_LEARN_USER_COURSE] PRIMARY KEY NONCLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE]  WITH CHECK ADD  CONSTRAINT [FK_CU_REF_COURSE] FOREIGN KEY([COURSE_ID])
REFERENCES [TRAINING].[YFSS_RES_COURSE] ([ID])
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE] CHECK CONSTRAINT [FK_CU_REF_COURSE]
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE]  WITH CHECK ADD  CONSTRAINT [FK_CU_REF_USER] FOREIGN KEY([USER_ID])
REFERENCES [TRAINING].[YFSS_SYS_USER] ([ID])
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE] CHECK CONSTRAINT [FK_CU_REF_USER]
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE] ADD  CONSTRAINT [DF__YFSS_LEAR__STUDY__4BAC3F29]  DEFAULT (’01′) FOR [STUDYSTATUS]
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE] ADD  CONSTRAINT [DF__YFSS_LEARN__TIME__4CA06362]  DEFAULT ((0)) FOR [TIME]
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE] ADD  CONSTRAINT [DF__YFSS_LEAR__HOURS__4D94879B]  DEFAULT ((0)) FOR [HOURS]
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE] ADD  CONSTRAINT [DF__YFSS_LEAR__STATU__4E88ABD4]  DEFAULT (’00′) FOR [STATUS]
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE] ADD  CONSTRAINT [DF_YFSS_LEARN_USER_COURSE_DELFLAG]  DEFAULT (’02′) FOR [DELFLAG]
GO

ALTER TABLE [TRAINING].[YFSS_LEARN_USER_COURSE] ADD  CONSTRAINT [DF__YFSS_LEAR__TERMY__664B26CC]  DEFAULT (’2010′) FOR [TERMYEAR]
GO