两个表横向合并

在销售出库毛利表报表中遇到了一个情况：

　　1、分别查询销售订单和调拨订单的数据

　　　　（1）、销售订单

　　　　

SELECT distinct
tbm.fnumber 物料编码
,tbm.fname_l2 物料名称
,tbm.fmodel 物料规格型号
,sum(tsse.fqty) 订单总数量
FROM T_IM_SaleIssueBill tis
left JOIN T_IM_SaleIssueEntry tise on tise.fparentid = tis.fid
right join T_SD_SaleOrder tss on tss.fnumber = tise.FSaleOrderNumber--销售订单
right join T_SD_SaleOrderEntry tsse on tsse.fparentid = tss.fid--销售订单分录
left join T_ORG_Storage tos on tos.fid = tise.FStorageOrgUnitID--库存组织
left join T_BD_Material tbm on tbm.fid = tise.FMaterialID
left join T_SCM_BillType tcb on tcb.fid = tise.FCoreBillTypeID
left join T_IM_TransferOrderBill tit on tit.fnumber = tise.fsaleordernumber--调拨订单
left join T_IM_TransferOrderBillEntry tite on tite.fparentid = tit.fid--调拨订单分录
where to_char(tis.fbizdate,'yyyy-MM-dd') <= '2018-07-04'
and to_char(tis.fbizdate,'yyyy-MM-dd') >= '2018-01-04'
and tos.fname_l2 = '电子器件厂'
and tis.fbasestatus ='4'
and tcb.fname_l2 in('销售订单')
--and tite.fqty is not null
--or tcb.fname_l2 in('调拨订单')
group by tbm.fnumber,tbm.fname_l2,tbm.fmodel

    　　    

              （2）、调拨订单

SELECT distinct
tbm.fnumber 物料编码
,tbm.fname_l2 物料名称
,tbm.fmodel 物料规格型号
,sum(tite.fqty) 订单总数量
FROM T_IM_SaleIssueBill tis
left JOIN T_IM_SaleIssueEntry tise on tise.fparentid = tis.fid
left join T_SD_SaleOrder tss on tss.fnumber = tise.FSaleOrderNumber--销售订单
left join T_SD_SaleOrderEntry tsse on tsse.fparentid = tss.fid--销售订单分录
left join T_ORG_Storage tos on tos.fid = tise.FStorageOrgUnitID--库存组织
inner join T_BD_Material tbm on tbm.fid = tise.FMaterialID
inner join T_SCM_BillType tcb on tcb.fid = tise.FCoreBillTypeID
left join T_IM_TransferOrderBill tit on tit.fnumber = tise.fsaleordernumber--调拨订单
left join T_IM_TransferOrderBillEntry tite on tite.fparentid = tit.fid--调拨订单分录
where to_char(tis.fbizdate,'yyyy-MM-dd') <= '2018-07-04'
and to_char(tis.fbizdate,'yyyy-MM-dd') >= '2018-01-04'
and tos.fname_l2 = '电子器件厂'
and tis.fbasestatus ='4'
--and tcb.fname_l2 in('销售订单')
and tcb.fname_l2 in('调拨订单')
--and tite.fqty is not null
group by tbm.fnumber,tbm.fname_l2,tbm.fmodel

　　　　

　　　　（3）、将两张表用union连接起来

SELECT distinct
tbm.fnumber 物料编码
,tbm.fname_l2 物料名称
,tbm.fmodel 物料规格型号
,sum(tsse.fqty) 订单总数量
FROM T_IM_SaleIssueBill tis
left JOIN T_IM_SaleIssueEntry tise on tise.fparentid = tis.fid
right join T_SD_SaleOrder tss on tss.fnumber = tise.FSaleOrderNumber--销售订单
right join T_SD_SaleOrderEntry tsse on tsse.fparentid = tss.fid--销售订单分录
left join T_ORG_Storage tos on tos.fid = tise.FStorageOrgUnitID--库存组织
left join T_BD_Material tbm on tbm.fid = tise.FMaterialID
left join T_SCM_BillType tcb on tcb.fid = tise.FCoreBillTypeID
left join T_IM_TransferOrderBill tit on tit.fnumber = tise.fsaleordernumber--调拨订单
left join T_IM_TransferOrderBillEntry tite on tite.fparentid = tit.fid--调拨订单分录
where to_char(tis.fbizdate,'yyyy-MM-dd') <= '2018-07-04'
and to_char(tis.fbizdate,'yyyy-MM-dd') >= '2018-01-04'
and tos.fname_l2 = '电子器件厂'
and tis.fbasestatus ='4'
and tcb.fname_l2 in('销售订单')
--and tite.fqty is not null
--or tcb.fname_l2 in('调拨订单')
group by tbm.fnumber,tbm.fname_l2,tbm.fmodel

union all



SELECT distinct
tbm.fnumber 物料编码
,tbm.fname_l2 物料名称
,tbm.fmodel 物料规格型号
,sum(tite.fqty) 订单总数量
FROM T_IM_SaleIssueBill tis
left JOIN T_IM_SaleIssueEntry tise on tise.fparentid = tis.fid
left join T_SD_SaleOrder tss on tss.fnumber = tise.FSaleOrderNumber--销售订单
left join T_SD_SaleOrderEntry tsse on tsse.fparentid = tss.fid--销售订单分录
left join T_ORG_Storage tos on tos.fid = tise.FStorageOrgUnitID--库存组织
inner join T_BD_Material tbm on tbm.fid = tise.FMaterialID
inner join T_SCM_BillType tcb on tcb.fid = tise.FCoreBillTypeID
left join T_IM_TransferOrderBill tit on tit.fnumber = tise.fsaleordernumber--调拨订单
left join T_IM_TransferOrderBillEntry tite on tite.fparentid = tit.fid--调拨订单分录
where to_char(tis.fbizdate,'yyyy-MM-dd') <= '2018-07-04'
and to_char(tis.fbizdate,'yyyy-MM-dd') >= '2018-01-04'
and tos.fname_l2 = '电子器件厂'
and tis.fbasestatus ='4'
--and tcb.fname_l2 in('销售订单')
and tcb.fname_l2 in('调拨订单')
--and tite.fqty is not null
group by tbm.fnumber,tbm.fname_l2,tbm.fmodel
) a
group by 物料编码

　　　　（4）、然后再进行分组

select *from 

(

 select 物料编码,sum(订单总数量) 订单总数量 from 

 (

 SELECT distinct 

 tbm.fnumber 物料编码

 ,tbm.fname_l2 物料名称

 ,tbm.fmodel   物料规格型号

 ,sum(tsse.fqty) 订单总数量

 FROM T_IM_SaleIssueBill tis 

 left JOIN   T_IM_SaleIssueEntry tise on tise.fparentid = tis.fid

 right join    T_SD_SaleOrder          tss  on tss.fnumber = tise.FSaleOrderNumber--销售订单

 right join    T_SD_SaleOrderEntry     tsse on tsse.fparentid = tss.fid--销售订单分录

 left join    T_ORG_Storage   tos  on tos.fid = tise.FStorageOrgUnitID--库存组织

 left join    T_BD_Material           tbm  on tbm.fid = tise.FMaterialID

 left join    T_SCM_BillType          tcb  on tcb.fid  = tise.FCoreBillTypeID

 left join   T_IM_TransferOrderBill  tit  on tit.fnumber = tise.fsaleordernumber--调拨订单

 left join    T_IM_TransferOrderBillEntry  tite on tite.fparentid = tit.fid--调拨订单分录

 where      to_char(tis.fbizdate,'yyyy-MM-dd')  <= '2018-07-04'

 and        to_char(tis.fbizdate,'yyyy-MM-dd')  >= '2018-01-04'

 and        tos.fname_l2 = '电子器件厂'

 and        tis.fbasestatus ='4'

 and        tcb.fname_l2 in('销售订单')

 --and        tite.fqty  is not null

 --or         tcb.fname_l2 in('调拨订单')

 group by tbm.fnumber,tbm.fname_l2,tbm.fmodel

 

 union all

 

 

 

 SELECT distinct 

 tbm.fnumber 物料编码

 ,tbm.fname_l2 物料名称

 ,tbm.fmodel   物料规格型号

 ,sum(tite.fqty) 订单总数量

 FROM T_IM_SaleIssueBill tis 

 left JOIN   T_IM_SaleIssueEntry tise on tise.fparentid = tis.fid

 left join    T_SD_SaleOrder          tss  on tss.fnumber = tise.FSaleOrderNumber--销售订单

 left join    T_SD_SaleOrderEntry     tsse on tsse.fparentid = tss.fid--销售订单分录

 left join    T_ORG_Storage   tos  on tos.fid = tise.FStorageOrgUnitID--库存组织

 inner join    T_BD_Material           tbm  on tbm.fid = tise.FMaterialID

 inner join    T_SCM_BillType          tcb  on tcb.fid  = tise.FCoreBillTypeID

 left join   T_IM_TransferOrderBill  tit  on tit.fnumber = tise.fsaleordernumber--调拨订单

 left join    T_IM_TransferOrderBillEntry  tite on tite.fparentid = tit.fid--调拨订单分录

 where      to_char(tis.fbizdate,'yyyy-MM-dd')  <= '2018-07-04'

 and        to_char(tis.fbizdate,'yyyy-MM-dd')  >= '2018-01-04'

 and        tos.fname_l2 = '电子器件厂'

 and        tis.fbasestatus ='4'

 --and      tcb.fname_l2 in('销售订单')

 and        tcb.fname_l2 in('调拨订单')

 --and tite.fqty  is not null

 group by tbm.fnumber,tbm.fname_l2,tbm.fmodel

 ) a

 group by 物料编码

 ) taq

select *from ( select 物料编码,sum(订单总数量) 订单总数量 from  ( SELECT distinct  tbm.fnumber 物料编码 ,tbm.fname_l2 物料名称 ,tbm.fmodel   物料规格型号 ,sum(tsse.fqty) 订单总数量 FROM T_IM_SaleIssueBill tis  left JOIN   T_IM_SaleIssueEntry tise on tise.fparentid = tis.fid right join    T_SD_SaleOrder          tss  on tss.fnumber = tise.FSaleOrderNumber--销售订单 right join    T_SD_SaleOrderEntry     tsse on tsse.fparentid = tss.fid--销售订单分录 left join    T_ORG_Storage   tos  on tos.fid = tise.FStorageOrgUnitID--库存组织 left join    T_BD_Material           tbm  on tbm.fid = tise.FMaterialID left join    T_SCM_BillType          tcb  on tcb.fid  = tise.FCoreBillTypeID left join   T_IM_TransferOrderBill  tit  on tit.fnumber = tise.fsaleordernumber--调拨订单 left join    T_IM_TransferOrderBillEntry  tite on tite.fparentid = tit.fid--调拨订单分录 where      to_char(tis.fbizdate,'yyyy-MM-dd')  <= '2018-07-04' and        to_char(tis.fbizdate,'yyyy-MM-dd')  >= '2018-01-04' and        tos.fname_l2 = '电子器件厂' and        tis.fbasestatus ='4' and        tcb.fname_l2 in('销售订单') --and        tite.fqty  is not null --or         tcb.fname_l2 in('调拨订单') group by tbm.fnumber,tbm.fname_l2,tbm.fmodel  union all    SELECT distinct  tbm.fnumber 物料编码 ,tbm.fname_l2 物料名称 ,tbm.fmodel   物料规格型号 ,sum(tite.fqty) 订单总数量 FROM T_IM_SaleIssueBill tis  left JOIN   T_IM_SaleIssueEntry tise on tise.fparentid = tis.fid left join    T_SD_SaleOrder          tss  on tss.fnumber = tise.FSaleOrderNumber--销售订单 left join    T_SD_SaleOrderEntry     tsse on tsse.fparentid = tss.fid--销售订单分录 left join    T_ORG_Storage   tos  on tos.fid = tise.FStorageOrgUnitID--库存组织 inner join    T_BD_Material           tbm  on tbm.fid = tise.FMaterialID inner join    T_SCM_BillType          tcb  on tcb.fid  = tise.FCoreBillTypeID left join   T_IM_TransferOrderBill  tit  on tit.fnumber = tise.fsaleordernumber--调拨订单 left join    T_IM_TransferOrderBillEntry  tite on tite.fparentid = tit.fid--调拨订单分录 where      to_char(tis.fbizdate,'yyyy-MM-dd')  <= '2018-07-04' and        to_char(tis.fbizdate,'yyyy-MM-dd')  >= '2018-01-04' and        tos.fname_l2 = '电子器件厂' and        tis.fbasestatus ='4' --and      tcb.fname_l2 in('销售订单') and        tcb.fname_l2 in('调拨订单') --and tite.fqty  is not null group by tbm.fnumber,tbm.fname_l2,tbm.fmodel ) a group by 物料编码 ) taq