连续使用多个union all 竟然一点儿也不慢。。。

连续使用多个union all 竟然一点儿也不慢。。。

--批次汇总表

select 日期,仓库编码,仓库,存货一类,存货大类,存货分类,存货编码,存货名称,规格型号,
主计量单位编码,计量单位名称,批号,净重,毛重,sum(入库数量) as 入库数量,sum(出库数量) as 出库数量
from(
--库存期初表
select convert(varchar(11),rd34.dDate,120) as 日期,rd34.cCode as 收发单据号,
rd34.cRdCode as 收发类别编码,rd34.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr34.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr34.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 入库数量,0 as 出库数量
from u8.UFDATA_账套文件夹.dbo.rdrecord34 rd34
left join u8.UFDATA_账套文件夹.dbo.rdrecords34 rdr34 on rd34.ID = rdr34.ID
left join u8.UFDATA_账套文件夹.dbo.Inventory inv on rdr34.cInvCode = inv.cInvCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join u8.UFDATA_账套文件夹.dbo.Warehouse war on rd34.cWhCode = war.cWhCode
union all
--材料出库单
select convert(varchar(11),rd.dDate,120) as 日期,rd.cCode as 收发单据号,
rd.cRdCode as 收发类别编码,rd.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
0 as 入库数量,iQuantity as 出库数量
from u8.UFDATA_账套文件夹.dbo.rdrecord11 rd
left join u8.UFDATA_账套文件夹.dbo.rdrecords11 rdr on rd.ID = rdr.ID
left join u8.UFDATA_账套文件夹.dbo.Inventory inv on rdr.cInvCode = inv.cInvCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join u8.UFDATA_账套文件夹.dbo.Inventory invf on rd.cPsPcode = invf.cInvCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invcf on invf.cInvCCode = invcf.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invcf1 on left(invf.cInvCCode,2) = invcf1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invcf2 on left(invf.cInvCCode,4) = invcf2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.Warehouse war on rd.cWhCode = war.cWhCode
union all
--产成品入库
select convert(varchar(11),rd10.dDate,120) as 日期,rd10.cCode as 收发单据号,
rd10.cRdCode as 收发类别编码,rd10.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr10.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr10.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 入库数量,0 as 出库数量
from u8.UFDATA_账套文件夹.dbo.rdrecord10 rd10
left join u8.UFDATA_账套文件夹.dbo.rdrecords10 rdr10 on rd10.ID = rdr10.ID
left join u8.UFDATA_账套文件夹.dbo.Inventory inv on rdr10.cInvCode = inv.cInvCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join u8.UFDATA_账套文件夹.dbo.Warehouse war on rd10.cWhCode = war.cWhCode
union all
--采购入库单
select convert(varchar(11),rd01.dDate,120) as 日期,rd01.cCode as 收发单据号,
rd01.cRdCode as 收发类别编码,rd01.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr01.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr01.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 入库数量,0 as 出库数量
from u8.UFDATA_账套文件夹.dbo.rdrecord01 rd01
left join u8.UFDATA_账套文件夹.dbo.rdrecords01 rdr01 on rd01.ID = rdr01.ID
left join u8.UFDATA_账套文件夹.dbo.Inventory inv on rdr01.cInvCode = inv.cInvCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join u8.UFDATA_账套文件夹.dbo.Warehouse war on rd01.cWhCode = war.cWhCode
union all
--其他入库单
select convert(varchar(11),rd08.dDate,120) as 日期,rd08.cCode as 收发单据号,
rd08.cRdCode as 收发类别编码,rd08.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr08.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr08.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 入库数量,0 as 出库数量
from u8.UFDATA_账套文件夹.dbo.rdrecord08 rd08
left join u8.UFDATA_账套文件夹.dbo.rdrecords08 rdr08 on rd08.ID = rdr08.ID
left join u8.UFDATA_账套文件夹.dbo.Inventory inv on rdr08.cInvCode = inv.cInvCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join u8.UFDATA_账套文件夹.dbo.Warehouse war on rd08.cWhCode = war.cWhCode
union all
--其他出库单
select convert(varchar(11),rd09.dDate,120) as 日期,rd09.cCode as 收发单据号,
rd09.cRdCode as 收发类别编码,rd09.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr09.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr09.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
0 as 入库数量,iQuantity as 出库数量
from u8.UFDATA_账套文件夹.dbo.rdrecord09 rd09
left join u8.UFDATA_账套文件夹.dbo.rdrecords09 rdr09 on rd09.ID = rdr09.ID
left join u8.UFDATA_账套文件夹.dbo.Inventory inv on rdr09.cInvCode = inv.cInvCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join u8.UFDATA_账套文件夹.dbo.Warehouse war on rd09.cWhCode = war.cWhCode
union all
--销售出库单
select convert(varchar(11),rd32.dDate,120) as 日期,rd32.cCode as 收发单据号,
rd32.cRdCode as 收发类别编码,rd32.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr32.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr32.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
0 as 入库数量,iQuantity as 出库数量
from u8.UFDATA_账套文件夹.dbo.rdrecord32 rd32
left join u8.UFDATA_账套文件夹.dbo.rdrecords32 rdr32 on rd32.ID = rdr32.ID
left join u8.UFDATA_账套文件夹.dbo.Inventory inv on rdr32.cInvCode = inv.cInvCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join u8.UFDATA_账套文件夹.dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join u8.UFDATA_账套文件夹.dbo.Warehouse war on rd32.cWhCode = war.cWhCode
) a
group by 日期,仓库编码,仓库,存货一类,存货大类,存货分类,存货编码,存货名称,规格型号,
主计量单位编码,计量单位名称,批号,净重,毛重

作为一个财务人员，代码处处是魔法。。。