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

关联表内只显示一行值
我统计货邮量 比如有两个表 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 a
go
create table a
(
 flt varchar(10),
 ori varchar(10),
 des varchar(10),
 pcs int,
 wgt int
)
go
insert into a
select 'cz0331','pek','los',10,100 union all
select 'cz0331','pek','dxb',20,200
go
if object_id('b') is not null
   drop table b
go
create table b
(
 flt varchar(10),
 mail_pcs int,
 mail_wgt int
)
go
insert into b
select 'cz0331',30,300
go
select 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) t2
on t1.flt=t2.flt and t1.row=t2.row
go
/*
flt        ori        des        pcs         wgt         mail_pcs    mail_wgt
---------- ---------- ---------- ----------- ----------- ----------- -----------
cz0331     pek        los        10          100         30          300
cz0331     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