有两张表
a 的字段 id uid score
2 1 2
3 1 3b 的字段 uid uname allscore
1 wang 5
2 li 0
两个表的uid关联
有没有sql语句能把uname是wang的分数和(score)更新到b表的wang的总成绩(allscore)中 也就是2+3=5
a 的字段 id uid score
2 1 2
3 1 3b 的字段 uid uname allscore
1 wang 5
2 li 0
两个表的uid关联
有没有sql语句能把uname是wang的分数和(score)更新到b表的wang的总成绩(allscore)中 也就是2+3=5
set b.allscore=t.sum_score
(select a.sum(score) from a,b where a.uid=b.uid and b.uname='wang')
where uname='wang';
create table `a`(
`id` int,
`uid` int,
`score` int
);
insert into`a` values(2,1,2);
insert into`a` values(3,1,3);DROP TABLE IF EXISTS test.b;
create table `b`(
`uid` int,
`uname` varchar(4),
`allscore` int
);
insert into`b` values(1,'wang',0);
insert into`b` values(2,'li',0);select * from a;
/**
id uid score
------ ------ ------
2 1 2
3 1 3
**/
select * from b;
/**
uid uname allscore
------ ------ --------
1 wang 0
2 li 0
**/update
b,
(select uid,sum(score) as score from a group by uid) as a
set
b.allscore=a.score
where
a.uid=b.uidselect * from b;
/**
uid uname allscore
------ ------ --------
1 wang 5
2 li 0
**/