Question

asp+sql查询重复记录的值的总和，更新并删除

表名order,字段和值如下
ID orderid money
6 201105 200
5 201108 160
4 201105 200
3 201101 10
2 201102 40
1 201108 160
要求吧orderid中值相同的money字段相加,并更新，正确结果如下
ID orderid money
6 201105 400
5 201108 320
4 201105 400
3 201101 10
2 201102 40
1 201108 320
急待求解！~！~！！！~！~！~！，可留下QQ，方便本人咨询谢谢

Answer 1

如果用一条语句来实现，我不行，用循环来实现我会。
语句如下
'把重复的ORDERID过滤掉，查找所有ID号。
sql="select distinct orderid from order"
rs.open sql,conn,1,1
do while not rs.eof
sql="UPDATE order SET money = (SELECT SUM(money) FROM order WHERE ORDERID = "&rs(0)&") where orderid="&rs(0)
conn.execute(sql)
rs.movenext
loop
rs.close
我把ORDERID当做数字来做的，如果是字符串的话，请加上单引号

Answer 2

一条语句：
update [order] set [money] = b.[money] from [order] as a,
(select orderid, sum([money]) as [money]
from [order] group by orderid) as b
where a.orderid = b.orderid