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_year
and a.score_term = b.score_term and a.score_edusys = b.score_edusys and a.score_item = b.score_item
and 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_score
go

drop table temp_min_score_id
go