工作中,因为原数据表设计的问题导致存在重复数据,表中存在部分列数据相同的重复记录,下面记录解决实际解决办法:删除重复记录(保留一条),并用这几列建立唯一索引,从而在数据库避免重复数据的插入。 /* 执行步骤: 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