我想删除表中重复的，求助
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
