两个表横向合并
在銷售出庫毛利表報表中遇到了一個情況：
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
轉載于:https://www.cnblogs.com/ygzs007/p/9275336.html
總結
- 上一篇： DHCP中继代理；DHCP突破vlan限
- 下一篇： 开发一个自己的 CSS 框架（二）