fjfdszj的专栏

无知者无畏

相关联的表,除了主键外其它相关处理(商品信息).a.BillNo=e.SrcBillNo and d.ProductCode=e.ProductCode and d.ProductSortCode=e.ProductSortCode
/*------------------------------------------------
 创建时间:2007-09-13
 创 建 人:szj
 参  考:
 功  能: 门店订货单执行明细表(仓库调拨单和加盟配送单)
 修  改: 
 具  体:
 备  注:
*/-------------------------------------------------
select a.BillNo,a.TheDate,e.*,b.TheName StorageName,b.ShopName,b.StorPlaceName,c.TheName ProductName,c.ConfigName,c.UnitName,
d.ProductCode,d.ProductSortCode,d.Number OrderNumber,d.DeliverDate
from tbSelfOrder a
left outer join tbSelfOrderItem d on a.BillNo=d.BillNo
left outer join
(
--仓库调拨单
select cc.ProductCode,cc.ProductSortCode,SrcBillNo,dd.BillNo RedeployNo,dd.ConfirmDate,dd.TheDate RedeployDate,sum(cc.Number) SureNumber,cc.RedeployInStorage,cc.RedeployInPlace
from tbStorRedeployItem cc
left outer join tbStorRedeploy dd on cc.BillNo = dd.BillNo
where dbo.GetByteToBoolean(dd.TheState,0x0001)=1 and dd.SrcBillSort = 1
group by cc.ProductCode,cc.ProductSortCode,cc.SrcBillNo,dd.BillNo,dd.ConfirmDate,dd.TheDate,cc.RedeployInStorage,cc.RedeployInPlace
union all
--加盟配送单
select cc.ProductCode,cc.ProductSortCode,SrcBillNo,dd.BillNo RedeployNo,dd.ConfirmDate,dd.TheDate RedeployDate,sum(cc.Number) SureNumber,cc.RedeployInStorage,cc.RedeployInPlace
from tbStorRedeployLeagueItem cc
left outer join tbStorRedeployLeague dd on cc.BillNo = dd.BillNo
where dbo.GetByteToBoolean(dd.TheState,0x0001)=1 and dd.SrcBillSort = 1
group by cc.ProductCode,cc.ProductSortCode,cc.SrcBillNo,dd.BillNo,dd.ConfirmDate,dd.TheDate,cc.RedeployInStorage,cc.RedeployInPlace
)e on a.BillNo=e.SrcBillNo and d.ProductCode=e.ProductCode and d.ProductSortCode=e.ProductSortCode
--辅助信息
left outer join vDatumStorPlace b on e.RedeployInStorage = b.TheCode and e.RedeployInPlace = b.StorPlaceCode
left outer join vDatumProduct c on d.ProductCode = c.TheCode and d.ProductSortCode = c.ProductSort
where dbo.GetByteToBoolean(a.TheState,0x0001)=1 and e.RedeployInStorage in {{StorageGroup}}  {0} 
阅读更多
文章标签: join c
个人分类: sql语句
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

相关联的表,除了主键外其它相关处理(商品信息).a.BillNo=e.SrcBillNo and d.ProductCode=e.ProductCode and d.ProductSortCode=e.ProductSortCode

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭