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