KIS专业版-查询分析：采购订单序时簿（一）

最新推荐文章于 2023-03-09 11:48:38 发布

leo9065

最新推荐文章于 2023-03-09 11:48:38 发布

阅读量1.7k

点赞数 1

分类专栏： K3 sql

本文链接：https://blog.csdn.net/weixin_39832767/article/details/89856607

版权

这是一段sql查询语句，是从sql profiler里跟踪出来的，对应的报表是采购订单序时簿，下面是原语句，这里会逐句讲解，分析每一句对应的作用

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select distinct top 40 
v1.FTranType AS FTranType,
v1.FCheckerID AS FCheckerID,
v1.FInterID AS FInterID,
u1.FEntryID AS FEntryID,
t3.FName AS FSupplyIDName,
v1.Fdate AS Fdate,
v1.FBillNo AS FBillNo,
v1.FPrintCount AS FPrintCount,
v1.FChangeDate AS FChangeDate,
v1.FChangeCauses AS FChangeCauses,
v1.FVersionNo AS FVersionNo,
case  when v1.FCheckerID>0 then 'Y' when v1.FCheckerID<0 then 'Y' else '' end  AS FCheck,
CASE WHEN v1.FStatus = 3 OR v1.FClosed = 1 THEN 'Y' ELSE '' END AS FClose,
t14.Fname AS FItemName,
t14.Fmodel AS FItemModel,
t17.FName AS FUnitIDName,
t8.FName AS FCurrencyIDName,
t10.FName AS FDeptIDName,
t11.FName AS FEmpIDName,
u1.FAuxQty AS FAuxQty,
ISNULL(u1.FDate, '') AS FSenddate,
t18.FName AS FBaseUnitID,
case when v1.FCancellation=1 then 'Y' else '' end AS FCancellation,
case when u1.FMRPClosed=1 then 'Y' else '' end AS FMRPClosed,
v1.FSupplyID AS FSupplyID,
t14.FQtyDecimal AS FQtyDecimal,
t14.FPriceDecimal AS FPriceDecimal,
t14.FNumber AS FFullNumber,
u1.FAuxPriceDiscount AS FAuxPriceDiscount,
u1.FAllAmount AS FAllAmount,
Case WHEN t14.FOrderUnitID=0 THEN '' Else  t500.FName end AS FCUUnitName,
Case When v1.FCurrencyID is Null Or v1.FCurrencyID='' then (Select FScale From t_Currency Where FCurrencyID=1) else t503.FScale end   AS FAmountDecimal,
u1.FReceiveAmountFor_Commit AS FReceiveAmountFor_Commit,
u1.FSecCoefficient AS FSecCoefficient,
u1.FSecQty AS FSecQty,
t505.FName AS FSecUnitName,
tplS.FStatusName AS FTplEStatus,
u1.FTplEFail AS FTplEFail,
tpl1.FWebTplName AS FWebTplType,
tpl.FWebtplName AS FWebtplID,
u1.FBarCode AS FBarCode,
(select top 1  CASE WHEN (t_Accessory.FFileSize <>0) then 'Y' else null end from t_Accessory where FItemID =v1.FInterID ) AS FHaveAccessory
,Case when u1.FAuxPropID = 0 Then (Case When ICInventory_Sub.FQty  is null Then 0 Else ICInventory_Sub.FQty / t19.FCoefficient End)
 Else (Case When ICInventory_SubWithAuxProp.FQty  is null Then 0 Else ICInventory_SubWithAuxProp.FQty / t19.FCoefficient End) End
 As FStockQtyOnlyForShow 
 from POOrder v1 INNER JOIN POOrderEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0 
 LEFT OUTER JOIN t_Supplier t3 ON     v1.FSupplyID = t3.FItemID   AND t3.FItemID <>0 
 INNER JOIN t_Currency t8