怎么将一个表中的统计出来的总数放到另外一个表中对应的列中?例如:表A中有三个字段,id,name,socre对应的记录有(1,'zs',89),(2,'zs',90),(3,'zs',89)(4,'ls',90)。表B中有三个字段id,name,count对应的记录为(1,'zs',null),(2,'ls',null)。怎么将表A中zs的记录总数添加到B中count对应zs的列,将ls在A中的记录总数添加到B中对应ls的count列
调试欢乐多
Table created
SQL> insert into a values(1,'zs',89);
1 row inserted
SQL> insert into a values(2,'zs',90);
1 row inserted
SQL> insert into a values(3,'zs',89);
1 row inserted
SQL> insert into a values(4,'ls',90);
1 row inserted
SQL> create table b (id int ,name varchar(10),count int) ;
Table created
SQL> insert into b values(1,'zs',null);
1 row inserted
SQL> insert into b values(2,'ls',null);
1 row inserted
SQL> select * from a ;
ID NAME SOCRE
----- ----- -----
1 zs 89
2 zs 90
3 zs 89
4 ls 90
SQL> select * from b ;
ID NAME COUNT
----- ----- -----
1 zs
2 ls
SQL> update b set count=(select count(*) from a where a.name = b.name) ;
2 rows updated
SQL> select * from b ;
ID NAME COUNT
----- ----- -----
1 zs 3
2 ls 1
SQL> drop table a purge ;
Table dropped
SQL> drop table b purge ;
Table droppedSQL>
set count =
(select z.counts
from (select name, count(1) counts from tab_a group by name) z
where b.name = z.name)
where exists (select z.counts
from (select name, count(1) counts from tab_a group by name) z
where b.name = z.name)
insert into A values(2,'zz',12);
insert into A values(3,'zz',42);
insert into A values(4,'zz',55);
insert into A values(5,'hh',55);
select * from a;
insert into B(id,name) values(1,'zz');
insert into B(id,name) values(2,'jj');
select *from b;update B set count=(select count(*) from a,b where a.name =b.name and b.name='zz' ) where b.name='zz'
。一进来就直接做了,做好看了下面回复,发现。
using (select name,sum(socre) as cout from hadd_a group by name) a
on (b.name = a.name)
when matched then
update set b.count = a.cout