求一句组合SQL
我有两个表,
A表:id,name,
B表:aid,num
a表内容
id name
1 a
2 a
3 a
4 b
5 b
6 c
b表内容
aid num
1 11
2 0
3 2
4 5
5 0
6 2
我要显示的内容为
name allnum
a 13
b 5
c 2
请问这样的组合SQL语句怎么样写,谢谢啦
[解决办法]
select a.name,sum(b.num) as allnum from a,b where a.id=b.id group by a.name
[解决办法]
create table A(id int, name varchar(10))
insert A select 1, 'a '
union all select 2, 'a '
union all select 3, 'a '
union all select 4, 'b '
union all select 5, 'b '
union all select 6, 'c '
create table B(aid int, num int)
insert B select 1, 11
union all select 2, 0
union all select 3, 2
union all select 4, 5
union all select 5, 0
union all select 6, 2
select A.name, allnum=sum(B.num) from A
inner join B on A.id=B.aid
group by A.name
--result
name allnum
---------- -----------
a 13
b 5
c 2
(3 row(s) affected)
