我统计货邮量 比如有两个表 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
cz0331 pek los 10 100 0 0
----------------------------------------
邮件在这个航班上只显示在一行,怎么处理a ,b 表还有其他的数据,
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
cz0331 pek los 10 100 0 0
----------------------------------------
邮件在这个航班上只显示在一行,怎么处理a ,b 表还有其他的数据,
之间的逻辑关系是什么样的?
cz0331 pek los 10 100 30 300
这行数据是只显示pcs=los的?先按你这个结果集给个SQL
select a.flt,a.ori,a.des,a.pcs,a.wgt,b.mail_pcs,b.mail_wgt
from a,b
where a.flt=b.flt
and a.des='los
union all
select a.flt,a.ori,a.des,a.pcs,a.wgt,0,0
from a
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
----------------------------------------
不知道我这次讲清楚没有
select * from
(select a.*,row_number() over (partition by flt order by ori,des, pcs,wgt) rn from a ) t1,
(select b.*,row_number() over (partition by flt order by mail_pcs,mail_wgt) rn from b ) t2
where t1.cz0331=t2.cz0331 and t1.rn=t2.rn
select t1.flt,t1.ori,t1.des, t1.pcs,t1.wgt,
decode(t1.rn,1,t2.des,0),decode(t1.rn,1,t2.wgt,0)
from
(select a.*,row_number() over (partition by flt order by ori,des, pcs,wgt) rn from a ) t1,b t2
where t1.flt = t2.flt
这样才能出现,0,0那条
好像有點錯誤 select t1.flt,t1.ori,t1.des, t1.pcs,t1.wgt,
decode(t1.rn,1,t2.mail_pcs,0),decode(t1.rn,1,t2.mail_wgt,0)
from
(select a.*,row_number() over (partition by flt order by ori,des, pcs,wgt) rn from a ) t1,b t2
where t1.flt = t2.flt