我想删除表中重复的，求助
collage1 collage2 weight
12727119131
12727121881
10120103101
10426117311
11303117671
12735117671
11992101821
12735122921
12727105221
11731127271
12475126481
12556127351
12556124751
12735126481
12735121881
10113120401
12122121421
12146113031
12146103081
10308104671
12735121181
10074114701
12507118791
12146127351
11992109201
12142122921
12186104761
10162117311
12188108541
12451121461
12735112111
12556121881
12494100741
10310124881
10875127351
12648122851
12483103191
首先这个表的weight（权重都是1），我没有设置主键，所以很多重复的，我想删除重复的，只保留一条记录，删除一条的同时，保留的那条weight（权重）加1，如果有相同记录10条，那么我就删除9条，但是weight权重要加9也就是权重是10. sql怎么写（可以一条一条的调到新表里），求代码
[最优解释]
select collage1,collage2,sum(weight) weight from 表 group by collage1,collage2
[其他解释]
select collage1,collage2,sum(weight) as weight into # from tb group by collage1,collage2
select * from #
truncate table tb
insert into tb select * FROM #
go
[其他解释]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-27 14:10:25
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([collage1] int,[collage2] int,[weight] int)
insert [test]
select 12727,11913,1 union all
select 12727,12188,1 union all
select 10120,10310,1 union all
select 10426,11731,1 union all
select 11303,11767,1 union all
select 12735,11767,1 union all
select 11992,10182,1 union all
select 12735,12292,1 union all
select 12727,10522,1 union all
select 11731,12727,1 union all
select 12475,12648,1 union all
select 12556,12735,1 union all
select 12556,12475,1 union all
select 12735,12648,1 union all
select 12735,12188,1 union all
select 10113,12040,1 union all
select 12122,12142,1 union all
select 12146,11303,1 union all
select 12146,10308,1 union all
select 10308,10467,1 union all
select 12735,12118,1 union all
select 10074,11470,1 union all
select 12507,11879,1 union all
select 12146,12735,1 union all
select 11992,10920,1 union all
select 12142,12292,1 union all
select 12186,10476,1 union all
select 10162,11731,1 union all
select 12188,10854,1 union all
select 12451,12146,1 union all
select 12735,11211,1 union all
select 12556,12188,1 union all
select 12494,10074,1 union all
select 10310,12488,1 union all
select 10875,12735,1 union all
select 12648,12285,1 union all
select 12483,10319,1
go
--你给的测试数据其实并没有重复的
--如果是需要这三个字段都重复了 才叫重复的话
select [collage1],[collage2],count(1)as [weight] into #test from test
group by [collage1],[collage2]
--然后再 ：
truncate table [test]
insert [test]
select * from #test
--如果说是只要第一个字段相同就算重复:
;with t
as(
select
px=row_number()over(partition by [collage1] order by getdate()),
*
from
test
)
select [collage1],[collage2],
(select max(px) from t b where a.collage1=b.collage1) into #tb from t a
--然后再 ：
truncate table [test]
insert test
select * from #tb
[其他解释]
collage1collage2weight
12727 11913 1
12727 12188 1
10120 10310 1
10426 11731 1
11303 11767 1
12735 11767 1
11992 10182 1
12735 12292 1
12727 10522 1
11731 12727 1
12475 12648 1
12556 12735 1
12556 12475 1
12735 12648 1
12735 12188 1
10113 12040 1
12122 12142 1
12146 11303 1
12146 10308 1
10308 10467 1
12735 12118 1
10074 11470 1
12507 11879 1
12146 12735 1
11992 10920 1
12142 12292 1
12186 10476 1
10162 11731 1
12188 10854 1
12451 12146 1
12735 11211 1
12556 12188 1
12494 10074 1
10310 12488 1
10875 12735 1
12648 12285 1
12483 10319 1
重新整理下图，美观下