不知道如何一起使用group by ，left join on

[小仙麦麦]

哪位高手帮我看看如何实现以下结果
表A
id a_name
1 a
2 b
3 c
表B
id b_money b_num
1 10 5
1 12 7
2 8 6
2 15 4
2 12 8
3 11 3
我想由这两个表得到
id a_name sum(b_money) sum(b_num)
1 a 22 12
2 b 35 18
3 c 11 3

我自己是想通过以下语句实现，但没办法通过编译，想知道为什么，不知道哪位高手能指点一下，非常感激
select B.id,A.a_name,sum(B.b_money),sum（B.b_num）
from B Group by B.id
left join A on A.id=B.id

[pt1314917]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
create table [A] (id int,a_name varchar(1))
insert into [A]
select 1,'a' union all
select 2,'b' union all
select 3,'c'
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (id int,b_money int,b_num int)
insert into [B]
select 1,10,5 union all
select 1,12,7 union all
select 2,8,6 union all
select 2,15,4 union all
select 2,12,8 union all
select 3,11,3

select a.*,b.moneys,b.nums from [A] left join 
(select id,moneys=sum(b_money),nums=sum(b_num) from b group by id)b
on a.id=b.id

[pt1314917]

--> 测试数据: [A]
if object_id('[A]') is not null drop table [A]
create table [A] (id int,a_name varchar(1))
insert into [A]
select 1,'a' union all
select 2,'b' union all
select 3,'c'
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (id int,b_money int,b_num int)
insert into [B]
select 1,10,5 union all
select 1,12,7 union all
select 2,8,6 union all
select 2,15,4 union all
select 2,12,8 union all
select 3,11,3

select a.*,moneys=sum(b_money),nums=sum(b_num) from [A] left join b
on a.id=b.id
group by a.id,a.a_name

--结果：
id          a_name moneys      nums
----------- ------ ----------- -----------
1           a      22          12
2           b      35          18
3           c      11          3

[--小F--]

select
 a.*, sum(b_money),sum(b_num) 
form
 a left join b
on
 a.id=b.id 
group by
 a.id,a.a_name 

[dawugui]

select a.id,a.a_name,isnull(sum(B.b_money),0) b_money,sinull(sum(B.b_num) ,0) b_num
from B left join A on A.id=B.id
Group by a.id , a.a_name

[小仙麦麦]

非常非常感谢！

[dawugui]

create table A(id int, a_name varchar(10))
insert into a values(1 ,     'a') 
insert into a values(2 ,     'b') 
insert into a values(3 ,     'c') 
create table B(id int,   b_money int, b_num int)
insert into b values(1 ,     10 ,       5 )
insert into b values(1 ,     12 ,       7 )
insert into b values(2 ,     8  ,      6 )
insert into b values(2 ,     15 ,       4 )
insert into b values(2 ,     12 ,       8 )
insert into b values(3 ,     11 ,       3 )
go

select a.id,a.a_name,isnull(sum(B.b_money),0) b_money,isnull(sum(B.b_num) ,0) b_num
from B left join A on A.id=B.id
Group by a.id , a.a_name

drop table a , b

/*
id          a_name     b_money     b_num       
----------- ---------- ----------- ----------- 
1           a          22          12
2           b          35          18
3           c          11          3

（所影响的行数为 3 行）
*/

[dawugui]

create table A(id int, a_name varchar(10))
insert into a values(1 ,     'a') 
insert into a values(2 ,     'b') 
insert into a values(3 ,     'c') 
create table B(id int,   b_money int, b_num int)
insert into b values(1 ,     10 ,       5 )
insert into b values(1 ,     12 ,       7 )
insert into b values(2 ,     8  ,      6 )
insert into b values(2 ,     15 ,       4 )
insert into b values(2 ,     12 ,       8 )
insert into b values(3 ,     11 ,       3 )
go

--1用a表left join b表.
select a.id,a.a_name,isnull(sum(B.b_money),0) b_money,isnull(sum(B.b_num) ,0) b_num
from A left join B on A.id=B.id
Group by a.id , a.a_name
/*
id          a_name     b_money     b_num       
----------- ---------- ----------- ----------- 
1           a          22          12
2           b          35          18
3           c          11          3

（所影响的行数为 3 行）
*/

--2用b表left join a表.
select b.id,a.a_name,isnull(sum(B.b_money),0) b_money,isnull(sum(B.b_num) ,0) b_num
from B left join A on B.id=A.id
Group by B.id , a.a_name
/*
id          a_name     b_money     b_num       
----------- ---------- ----------- ----------- 
1           a          22          12
2           b          35          18
3           c          11          3

（所影响的行数为 3 行）
*/
drop table a , b

[小仙麦麦]

很感谢大家的帮忙，我知道了，很高兴能在这学到东西