20110610A0905 生产领料序时簿

最新推荐文章于 2019-05-26 21:39:00 发布

原创最新推荐文章于 2019-05-26 21:39:00 发布 · 1.0k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#join #sql server #profiler #null

在 K/3 系统中界面如下：

查询语句如下：

select top 10 * from ( select * from ( select ROW_NUMBER() OVER (ORDER BY FDate DESC) AS RowNumber, t1.Fdate AS '日期' ,case when t1.FCheckerID <> 0 then '已审核' else '' end AS '审核标志' ,t3.FName AS '领料部门' ,t1.FBillNo AS '单据编号' ,t4.FName AS '发料仓库' ,t5.FNumber AS '物料长代码' ,t5.Fname AS '物料名称' ,t5.Fmodel AS '规格型号' ,t6.FName AS '计量单位' ,t2.FBatchNo AS '批号' ,cast(t2.Fauxqty as decimal(18,5)) AS '实发数量' ,cast(t2.Fauxprice as decimal(18,6)) AS '单价' ,t2.Famount AS '金额' ,t7.FName AS '领料' from ICStockBill t1 -- 主表 -- t2:分录 INNER JOIN ICStockBillEntry t2 ON t1.FInterID = t2.FInterID AND t2.FInterID <>0 -- t3:部门 LEFT OUTER JOIN t_Department t3 ON t1.FDeptID = t3.FItemID AND t3.FItemID <>0 -- t4:仓库 INNER JOIN t_Stock t4 ON t2.FSCStockID = t4.FItemID AND t4.FItemID <>0 -- t5:物料 INNER JOIN t_ICItem t5 ON t2.FItemID = t5.FItemID AND t5.FItemID <>0 -- t6:计量单位 INNER JOIN t_MeasureUnit t6 ON t2.FUnitID = t6.FItemID AND t6.FItemID <>0 -- t7:员工 LEFT OUTER JOIN t_Emp t7 ON t1.FSManagerID = t7.FItemID AND t7.FItemID <>0 where t1.FBillNo = 'SOUT011148') t WHERE ROWNUMBER > 10 * ( 1 - 1 ) ) t

执行结果：

使用 SQL Server Profiler 跟踪到的语句如下：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select top 40 u1.FDetailID AS FListEntryID,0 AS FSel,t14.FName AS FPlanVchTplName,t15.FName AS FActualVchTplName,v1.FPlanVchTplID AS FPlanVchTplID,v1.FActualVchTplID AS FActualVchTplID,v1.FVchInterID AS FVchInterID,v1.FTranType AS FTranType,v1.FInterID AS FInterID,u1.FEntryID AS FEntryID,v1.Fdate AS Fdate,case when v1.FCheckerID>0 then 'Y' when v1.FCheckerID<0 then 'Y' else '' end AS FCheck,case when v1.FCancellation=1 then 'Y' else '' end AS FCancellation,t4.FName AS FDeptIDName,v1.FBillNo AS FBillNo,t8.FName AS FSCStockIDName,t13.FNumber AS FFullNumber,t13.Fname AS FItemName,t13.Fmodel AS FItemModel,t16.FName AS FUnitIDName,u1.FBatchNo AS FBatchNo,u1.Fauxqty AS Fauxqty,u1.Fauxprice AS Fauxprice,u1.Famount AS Famount,t23.Fname AS FBaseUnit,t9.FName AS FSManagerIDName,(SELECT (SELECT FName FROM t_VoucherGroup WHERE FGroupID=t_Voucher.FGroupID)+'-'+CONVERT(Varchar(30),FNumber) FROM t_Voucher WHERE FVoucherid=v1.FVchInterID) AS FVoucherNumber,t13.FQtyDecimal AS FQtyDecimal,t13.FPriceDecimal AS FPriceDecimal,t30.FName AS FBaseUnitID,Case WHEN t13.FStoreUnitID=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,case when (v1.FROB <> 1) then 'Y' else '' end AS FRedFlag,t512.FName AS FSecUnitName,u1.FSecCoefficient AS FSecCoefficient,u1.FSecQty AS FSecQty from ICStockBill v1 INNER JOIN ICStockBillEntry u1 ON v1.FInterID = u1.FInterID AND u1.FInterID <>0 LEFT OUTER JOIN t_Department t4 ON v1.FDeptID = t4.FItemID AND t4.FItemID <>0 INNER JOIN t_Stock t8 ON u1.FSCStockID = t8.FItemID AND t8.FItemID <>0 LEFT OUTER JOIN t_Emp t9 ON v1.FSManagerID = t9.FItemID AND t9.FItemID <>0 INNER JOIN t_ICItem t13 ON u1.FItemID = t13.FItemID AND t13.FItemID <>0 INNER JOIN t_MeasureUnit t16 ON u1.FUnitID = t16.FItemID AND t16.FItemID <>0 INNER JOIN t_MeasureUnit t23 ON t13.FUnitID = t23.FItemID AND t23.FItemID <>0 LEFT OUTER JOIN t_MeasureUnit t30 ON t13.FUnitID = t30.FMeasureUnitID AND t30.FMeasureUnitID <>0 LEFT OUTER JOIN ICVoucherTpl t14 ON v1.FPlanVchTplID = t14.FInterID AND t14.FInterID <>0 LEFT OUTER JOIN ICVoucherTpl t15 ON v1.FActualVchTplID = t15.FInterID AND t15.FInterID <>0 LEFT OUTER JOIN t_MeasureUnit t500 ON t13.FStoreUnitID = t500.FItemID AND t500.FItemID <>0 LEFT OUTER JOIN t_Currency t503 ON v1.FCurrencyID = t503.FCurrencyID AND t503.FCurrencyID <>0 LEFT OUTER JOIN t_MeasureUnit t512 ON t13.FSecUnitID = t512.FItemID AND t512.FItemID <>0 where (v1.FInterID=545020 and u1.FEntryID=3) or (v1.FInterID=545020 and u1.FEntryID=4) or (v1.FInterID=545020 and u1.FEntryID=5) or (v1.FInterID=545020 and u1.FEntryID=6) or (v1.FInterID=545020 and u1.FEntryID=7) or (v1.FInterID=545020 and u1.FEntryID=8) or (v1.FInterID=545020 and u1.FEntryID=9) or (v1.FInterID=545020 and u1.FEntryID=10) or (v1.FInterID=545020 and u1.FEntryID=11) or (v1.FInterID=545020 and u1.FEntryID=12) or (v1.FInterID=545020 and u1.FEntryID=13) or (v1.FInterID=545020 and u1.FEntryID=14) or (v1.FInterID=545020 and u1.FEntryID=15) or (v1.FInterID=545020 and u1.FEntryID=16) or (v1.FInterID=545020 and u1.FEntryID=17) or (v1.FInterID=545020 and u1.FEntryID=18) or (v1.FInterID=545020 and u1.FEntryID=19) or (v1.FInterID=545020 and u1.FEntryID=20) or (v1.FInterID=545020 and u1.FEntryID=21) or (v1.FInterID=545020 and u1.FEntryID=22) or (v1.FInterID=545020 and u1.FEntryID=23) or (v1.FInterID=545020 and u1.FEntryID=24) or (v1.FInterID=545020 and u1.FEntryID=25) or (v1.FInterID=545020 and u1.FEntryID=26) or (v1.FInterID=545020 and u1.FEntryID=27) or (v1.FInterID=545020 and u1.FEntryID=28) or (v1.FInterID=545021 and u1.FEntryID=1) or (v1.FInterID=545021 and u1.FEntryID=2) or (v1.FInterID=545021 and u1.FEntryID=3) or (v1.FInterID=545021 and u1.FEntryID=4) or (v1.FInterID=545021 and u1.FEntryID=5) or (v1.FInterID=545021 and u1.FEntryID=6) or (v1.FInterID=545021 and u1.FEntryID=7) or (v1.FInterID=545021 and u1.FEntryID=8) or (v1.FInterID=545021 and u1.FEntryID=9) or (v1.FInterID=545021 and u1.FEntryID=10) or (v1.FInterID=545021 and u1.FEntryID=11) or (v1.FInterID=545021 and u1.FEntryID=12) or (v1.FInterID=545021 and u1.FEntryID=13) or (v1.FInterID=545021 and u1.FEntryID=14) order by v1.FInterID,u1.FEntryID