sqlserver 重复数据只留一条

sqlserver中多字段重复只保留一条的方法

表student_score中除主键之外 所有字段值都相同的则删除掉，只保留一条：

CREATE TABLE [dbo].[student_score]( [score_id] [numeric](11, 0) IDENTITY(1,1) NOT NULL primary key, [student_id] [varchar](16) NULL, [score_year] [int] NULL, [score_term] [int] NULL, [score_edusys] [int] NULL, [score_item] [int] NULL, [item_child] [int] NULL, [score_module] [varchar](2000) NULL, [score_value] [varchar](32) NULL)

-- 所有存在重复的都放在临时表中select * into temp_student_score from student_score a where exists(select 1 from student_score b where a.student_id = b.student_id and a.score_year= b.score_yearand a.score_term = b.score_term and a.score_edusys = b.score_edusys and a.score_item = b.score_itemand isnull(a.item_child,-1) = isnull(b.item_child,-1) and isnull(a.score_module,-1) = isnull(b.score_module,-1) and isnull(a.score_value,-1) = isnull(b.score_value,-1)and a.score_id <> b.score_id)

-- 重复数据中ID最小的一条数据存在临时表中select min(score_id) score_id into temp_min_score_id from temp_student_score group by student_id,score_year,score_term,score_edusys,score_item,item_child,score_module,score_value

-- 通过临时表删除数据

delete   a from student_score  a where exists(select 1 from temp_student_score b where a.score_id = b.score_id)and not exists(select 1 from temp_min_score_id c where a.score_id = c.score_id)

drop table temp_student_scorego

drop table temp_min_score_idgo

原文章浏览量：2634

原文章分类：数据库

原文链接：https://www.iteye.com/blog/lizhiyu-1118863