库存查询SQL语句，用到的相关技术

最新推荐文章于 2021-12-15 09:59:49 发布

czf2505

最新推荐文章于 2021-12-15 09:59:49 发布

阅读量752

点赞数

分类专栏： sql 文章标签： sql

本文链接：https://blog.csdn.net/czf2505/article/details/40786913

版权

sql 专栏收录该内容

30 篇文章

订阅专栏

这篇博客展示了如何使用SQL进行库存查询，包括POSINV、PO和库存移动记录的联合查询。通过示例，解释了如何利用常规查询、左连接和联合查询组合条件，简化SQL语句，提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Select 'POSINV' As DocTypeCode, h.InvoiceStatus As DocStatus, h.InvoiceNo As DocNo, h.MaskedInvoiceNo As ReferNo, h.InvoiceDate As TxDate, h.MarkDel HeaderMarkDel
, h.Shopcode As ReferShopCode, h.ShopCode As ActionShopCode, h.ShopMachineCode As MachineCode, d.PLU, d.QtySold As OrgQty, d.QtySold As FinialQty, -1 As Direction, d.MarkDel DetailMarkDel
From tblInvoiceH h Join tblInvoiceD d on h.ShopCode = d.ShopCode And h.InvoiceNo = d.InvoiceNo And h.SysID = d.InvoiceHSysID
UNION ALL
Select 'PO' DocTypeCode, poh.POStatus As DocStatus, poh.PoNo As DocNo, '' As ReferNo, poh.PORecDate As TxDate, poh.MarkDel HeaderMarkDel
, poh.OrderShopCode As ReferShopCode, pod.DlvyShopCode ActionShopCode, poh.MachineCode, pod.PLU, pod.SupplierQty As OrgQty, pod.RecSupplierQty As FinialQty, 1 As Direction, pod.MarkDel DetailMarkDel
From tblPOHeader poh Join tblPODetails pod on poh.OrderShopCode = pod.OrderShopCode And poh.PONO = pod.PONO
UNION ALL
Select smh.DocTypeCode, smh.MovementStatus DocStatus, smh.MovementNo As DocNo, smh.OrgNo As ReferNo, smh.MovementDate As TxDate, smh.MarkDel HeaderMarkDel
, Case When smh.DocTypeCode = 'GR' Then smh.FromSupplier When smh.DocTypeCode = 'TO' Then smh.ToShop   When smh.DocTypeCode = 'TI' Then smh.FromShop When smh.DocTypeCode = 'SR' Then smh.ToSupplier End ReferShopCode
, Case When smh.DocTypeCode = 'GR' Then smh.ToShop         When smh.DocTypeCode = 'TO' Then smh.FromShop When smh.DocTypeCode = 'TI' Then smh.ToShop   When smh.DocTypeCode = 'SR' Then smh.FromShop End ActionShopCode
, smh.MachineCode
, smd.PLU, smd.OrgQty, smd.MovementQty FinialQty, smh.MovementDirection, smd.MarkDel DetailMarkDel
From tblStockMovementHeader smh Join tblStockMovementDetails smd on smh.MovementNo = smd.MovementNo and smh.MovementShopCode = smd.MovementShopCode
UNION ALL
Select wh.DocType As DocTypeCode, wh.Status As DocStatus, wh.DocNo, '' As ReferNo, wh.OrderDate As TxDate, wh.MarkDel HeaderMarkDel, wh.OrderShopCode As ReferShopCode
,wh.OrderShopCode As ActionShopCode, wh.MachineCode, wd.PLU, wd.Qty As OrgQty, wd.Qty As FinialQty, -1 As Direction, wd.MarkDel As DetailMarkDel
From WStblOrderH wh Join WStblOrderD wd on wh.OrderHID = wd.OrderHID And wh.OrderShopCode = wd.ShopCode




这个例子，用到了常规查询，左右连接，联合查询等基础知识。最显著的特点，将查询筛选条件作为一个查询选项表示出来，这样可以大大简化了SQL语句（相对于在SQL里面自
己写死匹配条件）。