日期:2014-05-18  浏览次数:20460 次

求一句组合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)