有表table1,里面有几千条数据。
table1有两个字段,username(varchar(50)),prize(float)。username prize
user1 2.5
user1 3.1
user1 4.1
user2 1.2
user2 3.5
user2 6.5
user3 ..
.. ..
.. ..现在要求相同username的prize值相加。
结果如下:
username prize
user1 9.7
user1 9.7
user1 9.7
user2 11.2
user2 11.2
user2 11.2
user3 ..
.. ..
.. ..求该修改语句。
table1有两个字段,username(varchar(50)),prize(float)。username prize
user1 2.5
user1 3.1
user1 4.1
user2 1.2
user2 3.5
user2 6.5
user3 ..
.. ..
.. ..现在要求相同username的prize值相加。
结果如下:
username prize
user1 9.7
user1 9.7
user1 9.7
user2 11.2
user2 11.2
user2 11.2
user3 ..
.. ..
.. ..求该修改语句。
update
tb
set
prize=(select sum(prize) from tb group by username)
into #
from table1
group by username
update table1
set prize=#.sumprize
from # join table1 on #.username=table1.username
update table1
set prize=(select sum(prize) from table1 where t.username=username)
from table1 t
T.username,
T1.sumprize
FROM
TABLE1 T JOIN
(SELECT username,SUM(prize) as sumprize FROM TABLE1 ) AS T1
ON T.username=T1.username,
T.username,
T1.sumprize
FROM
TABLE1 T JOIN
(SELECT username,SUM(prize) as sumprize FROM TABLE1 ) AS T1
ON T.username=T1.username