我在配置单元中有3个表sourcetable、mappingtable和destinationtable。
这里我根据连接条件连接前两个表。但是当我试图用第三个表执行右连接时,我遇到了一些问题。“目的地表”中要遵循的标准。
应该只获取3月5日的数据,并且应该包含源表中的匹配行以及目标表中的其余行。我使用的查询如下。左联接工作正常。但当我尝试加入目标表时,它是不正确的。
我提供了表关系和outputtable中要实现的输出。任何形式的指导都是值得赞赏的。
(select a.Account as Accounts, a.customer as customers,a.productci,b.product_cr as Productcr,sum(a.cost)/sum(a.quantity) as perprice,
from sourcetable a
LEFT join mappingtable b on( a.productci = b.product_ci ) where (a.createddate >= '2018-03-01' and a.createddate < '2018-03-06')
GROUP BY a.customer,a.Account,a.productci,b.product_cr order by a.Account,a.customer) AS TEST
RIGHT JOIN destinationtable Temp on(TEST.Productcr=Temp.productgr) where temp.createddate='2018-03-05'
GROUP BY temp.customer,temp.Account,temp.productgr
表格详情如下所示
源表
Account,customer,productci,quantity,cost,Createddate
A01,S01,100,3000,120000,3/5/2018
A02,S02,140,4000,180000,3/4/2018
Map表
product_ci,product_cr,proddesc
100,4991089,Product1
140,4991090,Product2
150,4991091,Product3
120,4991092,Product4
目标表
Account,customer,productgr,volume,placeid,Createddate
A01,S01,4991089,1890000,P01,3/5/2018
A02,S02,4991090,1890001,P02,3/5/2018
A03,S03,4991091,1890002,P03,3/5/2018
A04,S04,4991092,1890003,P04,3/5/2018
A05,S05,4991093,1890004,P05,3/5/2018
output:result
Account,customer,productgr,volume,Perprice,placeid
A01,S01,4991089,1890000,40,P01
A02,S02,4991090,1890001,45,P02
A03,S03,4991091,1890002,285.1002865,P03
A04,S04,4991092,1890003,30,P04
A05,S05,4991093,1890004,1170.113097,P05
暂无答案!
目前还没有任何答案,快来回答吧!