多表查询问题（在线等）
有两张表a,b
结构分别如下
a表（其中两个字段）
18401 1000
18402 1000
18403 500
18403 1000
18401 500
null 1000
b表
18401 武汉
18402 黄冈
18403 长沙
如何编写SQL语句实现如下效果
武汉 1500
黄冈 1000
长沙 1500
其他 1000
希望高手路过顺手解答，解决问题可以另外加分，在线等
[解决办法]
declare @a table (col1 int,col2 int)
declare @b table (col1 int,col2 char(4))
insert @a
select 18401,1000 union all
select 18402,1000 union all
select 18403,500 union all
select 18403,1000 union all
select 18401,500 union all
select null,1000
insert @b
select 18401, '武汉 ' union all
select 18402, '黄冈 ' union all
select 18403, '长沙 '
select
isnull(b.col2, '其他 '),
sum(a.col2)
from @a a
left join @b b
on b.col1 = a.col1
group by b.col2
order by b.col2
[解决办法]
create table a(id varchar(20),money int)
create table b(id varchar(20),name varchar(20))
insert a select '18401 ',1000
union all select '18402 ',1000
union all select '18403 ',500
union all select '18403 ',1000
union all select '18401 ',500
union all select null,1000
insert b select '18401 ', '武汉 '
union all select '18402 ', '黄冈 '
union all select '18403 ', '长沙 '
select name=isnull(name, '其他 '),m.money from b right join
(
select id,money=sum(money) from a group by id
)m
on b.id=m.id
drop table a,b
[解决办法]
select isnull(b.col2, '其它 '),sum(a.col2) from a left join b on a.col1=b.col1 group by b.col2
[解决办法]
SELECT SUM(a.money) AS money, b.name
FROM a LEFT OUTER JOIN
b ON b.id = a.id
GROUP BY b.name
