关联表内只显示一行值,该如何解决

关联表内只显示一行值
我统计货邮量 比如有两个表 a( flt,ori,des, pcs,wgt) b(flt,mail_pcs,mail_wgt)  
a表中有两行数据是  
-----------------------  
cz0331 pek los 10 100  
cz0331 pek dxb 20 200  
-----------------------  
b 表中有一行数据是  
------------------  
CZ0331 30 300  
---------------  

我想要结果  
flt ori des pcs wgt mail_pcs mail_wgt  
-----------------------------------  
cz0331 pek los 10 100 30 300  
cz0331 pek dxb 20 200 0 0  
----------------------------------------  
邮件在这个航班上只显示在一行， 怎么处理  
两个表的连接关键字是flt，如果直接关联用语句select a.*, b.mail_pcs, b.mail_wgt from a, b where a.flt=b.flt的话
会显示
  flt ori des pcs wgt mail_pcs mail_wgt  
-----------------------------------  
cz0331 pek los 10 100 30 300  
cz0331 pek dxb 20 200 30 300  
----------------------------------------  
而我想要的结果是
-----------------------------------  
cz0331 pek los 10 100 30 300  
cz0331 pek dxb 20 200 0 0  
----------------------------------------

[解决办法]

SQL code

elect a.*, b.mail_pcs, b.mail_wgt from b left join a on a.flt=b.flt
[解决办法]
SQL code
select a.*, b.mail_pcs, b.mail_wgt from b left join a on a.flt=b.flt
[解决办法]
SQL code
select a.*, isnull(b.mail_pcs,0), insull(b.mail_wgt,0) from b left join a on a.flt=b.flt
[解决办法]
SQL code
if object_id('a') is not null   drop table agocreate table a( flt varchar(10), ori varchar(10), des varchar(10), pcs int, wgt int)goinsert into aselect 'cz0331','pek','los',10,100 union allselect 'cz0331','pek','dxb',20,200goif object_id('b') is not null   drop table bgocreate table b( flt varchar(10), mail_pcs int, mail_wgt int)goinsert into bselect 'cz0331',30,300goselect t1.flt,ori,des,pcs,wgt,mail_pcs=isnull(mail_pcs,0),mail_wgt=isnull(mail_wgt,0) from (select * ,row=row_number() over(partition by flt order by getdate()) from a)t1 left join (select *,row=row_number() over(partition by flt order by getdate()) from b) t2on t1.flt=t2.flt and t1.row=t2.rowgo/*flt        ori        des        pcs         wgt         mail_pcs    mail_wgt---------- ---------- ---------- ----------- ----------- ----------- -----------cz0331     pek        los        10          100         30          300cz0331     pek        dxb        20          200         0           0(2 行受影响)*/
[解决办法]
探讨

SQL code

select a.*, isnull(b.mail_pcs,0), insull(b.mail_wgt,0) from b left join a on a.flt=b.flt