collage1 collage2 weight
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
首先这个表的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