关于外连接的问题

[vbkid]

我有两张表:
A表
htbh htlx htje
0001 1 5000.00
0002 1 8000.00
...
B表
htbh skbh ysje payed
0001 01 2000.00 2000.00
0001 02 1500.00 1000.00
0001 03 1500.00

我想查询出 A.htbh,A.htlx,A.htje,SUM(B.payed)

[saucer]

create table A (htbh varchar(10), htlx int, htje decimal)
insert into A values ('0001', 1, 5000.00)
insert into A values ('0002', 1, 8000.00)

create table B (htbh varchar(10), skbh varchar(10), ysje decimal, payed decimal)
insert into B values('0001', '01', 2000.00, 2000.00)
insert into B values('0001', '02', 1500.00, 1000.00)
insert into B values('0001', '03', 1500.00,null)
insert into B values('0002', '04', 1500.00,100)

select A.htbh, A.htlx, A.htje, sum(B.payed) as sum
from A, B
where A.htbh = B.htbh
group by A.htbh, A.htlx, A.htje

--or

select htbh, htlx, htje, (select sum(payed) from B where B.htbh = A.htbh) as sum
from A

[chenyubang]

select A.htbh, A.htlx, A.htje, sum(B.payed)
from A , B
where A.htbh = B.htbh
group by A.htbh, A.htlx, A.htje;

这样就可以了

^_______.^

[伍子V5]

select A.htbh, A.htlx, A.htje, sum(isnull(B.payed,0)) as sum
from A
inner join B
on A.htbh = B.htbh
group by A.htbh, A.htlx, A.htje

[pengdali]

select *,(select sum(payed) from b表 where htbh=a表.htbh) 和 from a表

或：

select A.htbh,A.htlx,A.htje,sum(b.payed) 和 from a表 left join b表 on a表.htbh=b表.htbh group A.htbh,A.htlx,A.htje

[vbkid]

忘了告诉你们其实我要选择的远不止这些字段,所以用group by字句太麻烦,可是用自查询好像查询效率会明显低下来.不过至少我知道目前没有更好的方法了!谢谢各位!