通常计算制程直通率的时，需要用到累乘积计算功能，在EXCEL表中比较容易，在SQL中如何使用呢？
当前直通率=上一制程直通率*本制程直通率。
(1)循环遍历,游标(采用游标代码量比较多，而且效率低)
(2)SQL执行(需要08R2以上，我司目前是14版)EXP(sum(log([A])) over(order by XX ASC))
USE [OA] GO /****** Object: StoredProcedure [dbo].[DZ_ZRInspectionItemsDailyRpt] Script Date: 2020/3/9 9:09:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: <动态生成检验报表> -- ============================================= ALTER Procedure [dbo].[DZ_ZRInspectionItemsDailyRpt] ( @ProductSeries AS NVARCHAR(20) , @YearMonth AS CHAR(6) ) AS BEGIN IF OBJECT_ID('tempdb..#T1') IS NOT NULL BEGIN DROP TABLE #T1 END IF OBJECT_ID('tempdb..#T2') IS NOT NULL BEGIN DROP TABLE #T2 END IF OBJECT_ID('tempdb..#T3') IS NOT NULL BEGIN DROP TABLE #T3 END IF OBJECT_ID('tempdb..#T4') IS NOT NULL BEGIN DROP TABLE #T4 END IF OBJECT_ID('tempdb..#T5') IS NOT NULL BEGIN DROP TABLE #T5 END IF OBJECT_ID('tempdb..#T6') IS NOT NULL BEGIN DROP TABLE #T6 END IF OBJECT_ID('tempdb..#T7') IS NOT NULL BEGIN DROP TABLE #T7 END IF OBJECT_ID('tempdb..#ZT') IS NOT NULL BEGIN DROP TABLE #ZT END ------产品品品收集--------------- CREATE TABLE #T1 ( RouteCode CHAR(20), RouteName CHAR(20), BadItem CHAR(20), [01] CHAR(100), [02] CHAR(100), [03] CHAR(100), [04] CHAR(100), [05] CHAR(100), [06] CHAR(100), [07] CHAR(100), [08] CHAR(100), [09] CHAR(100), [10] CHAR(100), [11] CHAR(100), [12] CHAR(100), [13] CHAR(100), [14] CHAR(100), [15] CHAR(100), [16] CHAR(100), [17] CHAR(100), [18] CHAR(100), [19] CHAR(100), [20] CHAR(100), [21] CHAR(100), [22] CHAR(100), [23] CHAR(100), [24] CHAR(100), [25] CHAR(100), [26] CHAR(100), [27] CHAR(100), [28] CHAR(100), [29] CHAR(100), [30] CHAR(100), [31] CHAR(100) ) ------产品品品收集--------------- CREATE TABLE #T2 ( RouteCode CHAR(20), RouteName CHAR(20), BadItem CHAR(20), [01] DECIMAL(18,4) DEFAULT(0), [02] DECIMAL(18,4) DEFAULT(0), [03] DECIMAL(18,4) DEFAULT(0), [04] DECIMAL(18,4) DEFAULT(0), [05] DECIMAL(18,4) DEFAULT(0), [06] DECIMAL(18,4) DEFAULT(0), [07] DECIMAL(18,4) DEFAULT(0), [08] DECIMAL(18,4) DEFAULT(0), [09] DECIMAL(18,4) DEFAULT(0), [10] DECIMAL(18,4) DEFAULT(0), [11] DECIMAL(18,4) DEFAULT(0), [12] DECIMAL(18,4) DEFAULT(0), [13] DECIMAL(18,4) DEFAULT(0), [14] DECIMAL(18,4) DEFAULT(0), [15] DECIMAL(18,4) DEFAULT(0), [16] DECIMAL(18,4) DEFAULT(0), [17] DECIMAL(18,4) DEFAULT(0), [18] DECIMAL(18,4) DEFAULT(0), [19] DECIMAL(18,4) DEFAULT(0), [20] DECIMAL(18,4) DEFAULT(0), [21] DECIMAL(18,4) DEFAULT(0), [22] DECIMAL(18,4) DEFAULT(0), [23] DECIMAL(18,4) DEFAULT(0), [24] DECIMAL(18,4) DEFAULT(0), [25] DECIMAL(18,4) DEFAULT(0), [26] DECIMAL(18,4) DEFAULT(0), [27] DECIMAL(18,4) DEFAULT(0), [28] DECIMAL(18,4) DEFAULT(0), [29] DECIMAL(18,4) DEFAULT(0), [30] DECIMAL(18,4) DEFAULT(0), [31] DECIMAL(18,4) DEFAULT(0) ) DECLARE @days INT ,@everyday AS CHAR(8) DECLARE @ItemName AS CHAR(100) INSERT INTO #T1 ( RouteCode , RouteName , BadItem , [01] , [02] , [03] , [04] , [05] , [06] , [07] , [08] , [09] , [10] , [11] , [12] , [13] , [14] , [15] , [16] , [17] , [18] , [19] , [20] , [21] , [22] , [23] , [24] , [25] , [26] , [27] , [28] , [29] , [30] , [31] ) VALUES ( '0.工序代码' , -- RouteName - char(20) '0.工序名称' , -- RouteName - char(20) '产品品名' , -- BadItem - char(20) '' , -- 01 - char(100) '' , -- 02 - char(100) '' , -- 03 - char(100) '' , -- 04 - char(100) '' , -- 05 - char(100) '' , -- 06 - char(100) '' , -- 07 - char(100) '' , -- 08 - char(100) '' , -- 09 - char(100) '' , -- 10 - char(100) '' , -- 11 - char(100) '' , -- 12 - char(100) '' , -- 13 - char(100) '' , -- 14 - char(100) '' , -- 15 - char(100) '' , -- 16 - char(100) '' , -- 17 - char(100) '' , -- 18 - char(100) '' , -- 19 - char(100) '' , -- 20 - char(100) '' , -- 21 - char(100) '' , -- 22 - char(100) '' , -- 23 - char(100) '' , -- 24 - char(100) '' , -- 25 - char(100) '' , -- 26 - char(100) '' , -- 27 - char(100) '' , -- 28 - char(100) '' , -- 29 - char(100) '' , -- 30 - char(100) '' -- 31 - char(100) ) SET @days=0 DECLARE @SQL CHAR(200),@2day AS CHAR(2) --遍历当月每天 while @days<31 BEGIN SET @days=@days+1 SET @2day=RIGHT(@days+100,2) SET @everyday=@YearMonth+@2day --汇总每天产品型号 SELECT @ItemName=STUFF((select '、' + ItemName from R_RouteMaster where CONVERT(CHAR(8),InspectionDate,112)=@everyday FOR XML PATH('')),1,1,''); --更新每天的产品型号 IF(@ItemName<>'') BEGIN SET @SQL='UPDATE #T1 SET ['+@2day+']='''+RTRIM(@ItemName)+''' where BadItem='+'''产品品名''' --PRINT @SQL EXECUTE (@SQL) END END --1.实际输出数量 SELECT RouteCode,Route AS RouteName,'1.实际输出数量' AS BadItem,SUM(InspectionQty) InspectionQty,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday INTO #T3 FROM dbo.R_RouteDetail LEFT JOIN dbo.R_RouteMaster ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId WHERE ProductType=@ProductSeries AND CONVERT(CHAR(6),InspectionDate,112)=@YearMonth AND R_RouteDetail.InspectionQty<>0 GROUP BY RouteCode,Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) --2.不良数量 SELECT RouteCode,Route AS RouteName,'2.不良数量' AS BadItem,SUM(BadQty) BadQty,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday INTO #T4 FROM dbo.R_RouteDetail LEFT JOIN dbo.R_RouteMaster ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId WHERE ProductType=@ProductSeries AND CONVERT(CHAR(6),InspectionDate,112)=@YearMonth AND R_RouteDetail.InspectionQty<>0 GROUP BY RouteCode,Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) --3.不良数量cast(cast(SUM(R_RouteDetail.BadQty)*1.0/SUM(R_RouteDetail.InspectionQty)*100 as decimal(10,2)) as varchar)+'%' AS BadRate SELECT RouteCode,Route AS RouteName,'3.不良率' AS BadItem, SUM(R_RouteDetail.BadQty)*1.0000/SUM(R_RouteDetail.InspectionQty) BadRate,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday INTO #T5 FROM dbo.R_RouteDetail LEFT JOIN dbo.R_RouteMaster ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId WHERE ProductType=@ProductSeries AND CONVERT(CHAR(6),InspectionDate,112)=@YearMonth AND R_RouteDetail.InspectionQty<>0 GROUP BY RouteCode,Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) --4. 直通率先占位默认值 SELECT RouteCode,Route AS RouteName,'4.直通率' AS BadItem,CASE WHEN SUM(R_RouteDetail.InspectionQty) =0 THEN 1 ELSE (SUM(R_RouteDetail.InspectionQty)-SUM(R_RouteDetail.BadQty)*1.0000)/SUM(R_RouteDetail.InspectionQty) END AS ZTRate, SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday INTO #T6 FROM dbo.R_RouteDetail LEFT JOIN dbo.R_RouteMaster ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId WHERE ProductType=@ProductSeries AND CONVERT(CHAR(6),InspectionDate,112)=@YearMonth AND R_RouteDetail.InspectionQty<>0 GROUP BY RouteCode,Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) ---不良项目按工序和日期汇总 SELECT R_RouteDetail.RouteCode,R_RouteDetail.Route,InspectionItems AS InspectionItems,SUM(R_RouteInspection.BadQty) AS BadQty ,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2) AS eday INTO #T7 FROM dbo.R_RouteMaster LEFT JOIN dbo.R_RouteDetail ON dbo.R_RouteMaster.Id = R_RouteDetail.MasterId LEFT JOIN dbo.R_RouteInspection ON R_RouteInspection.DetailId = R_RouteDetail.Id WHERE ProductType=@ProductSeries AND CONVERT(CHAR(6),InspectionDate,112)=@YearMonth AND R_RouteInspection.BadQty<>0 GROUP BY R_RouteDetail.RouteCode,R_RouteDetail.Route,SUBSTRING(CONVERT(CHAR(8),InspectionDate,112),7,2),InspectionItems INSERT INTO #T2 SELECT * FROM #T3 /*数据源*/ AS P PIVOT ( MAX(InspectionQty) FOR P.eday IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) ) AS T INSERT INTO #T2 SELECT * FROM #T4 /*数据源*/ AS P PIVOT ( MAX(BadQty) FOR P.eday/*需要行转列的列*/ IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) ) AS T INSERT INTO #T2 SELECT * FROM #T5 /*数据源*/ AS P PIVOT ( MAX(BadRate) FOR P.eday/*需要行转列的列*/ IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) ) AS T INSERT INTO #T2 SELECT * FROM #T6 /*数据源*/ AS P PIVOT ( MAX(ZTRate) FOR P.eday/*需要行转列的列*/ IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) ) AS T INSERT INTO #T2 SELECT * FROM #T7 /*数据源*/ AS P PIVOT ( MAX(BadQty) FOR P.eday/*需要行转列的列*/ IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]) ) AS T ----直通率临时表 SELECT row_number() over(ORDER BY RouteCode,BadItem) AS id ,RouteCode,RouteName,BadItem, ISNULL([01],1) [01],ISNULL([02],1) [02],ISNULL([03],1) [03],ISNULL([04],1) [04],ISNULL([05],1) [05],ISNULL([06],1) [06],ISNULL([07],1) [07],ISNULL([08],1) [08], ISNULL([09],1) [09],ISNULL([10],1) [10],ISNULL([11],1) [11],ISNULL([12],1) [12],ISNULL([13],1) [13],ISNULL([14],1) [14],ISNULL([15],1) [15],ISNULL([16],1) [16], ISNULL([17],1) [17],ISNULL([18],1) [18],ISNULL([19],1) [19],ISNULL([20],1) [20],ISNULL([21],1) [21],ISNULL([22],1) [22],ISNULL([23],1) [23],ISNULL([24],1) [24], ISNULL([25],1) [25],ISNULL([26],1) [26],ISNULL([27],1) [27],ISNULL([28],1) [28],ISNULL([29],1) [29],ISNULL([30],1) [30],ISNULL([31],1) [31] INTO #ZT FROM #T2 WHERE BadItem='4.直通率' ORDER BY RouteCode, RouteName,BadItem ASC ---更新累积---- UPDATE #T2 SET #T2.[01]=B.[01],#T2.[02]=B.[02],#T2.[03]=B.[03],#T2.[04]=B.[04],#T2.[05]=B.[05],#T2.[06]=B.[06],#T2.[07]=B.[07],#T2.[08]=B.[08], #T2.[09]=B.[09],#T2.[10]=B.[10],#T2.[11]=B.[11],#T2.[12]=B.[12],#T2.[13]=B.[13],#T2.[14]=B.[14],#T2.[15]=B.[15],#T2.[16]=B.[16], #T2.[17]=B.[17],#T2.[18]=B.[18],#T2.[19]=B.[19],#T2.[20]=B.[20],#T2.[21]=B.[21],#T2.[22]=B.[22],#T2.[23]=B.[23],#T2.[24]=B.[24], #T2.[25]=B.[25],#T2.[26]=B.[26],#T2.[27]=B.[27],#T2.[28]=B.[28],#T2.[29]=B.[29],#T2.[30]=B.[30],#T2.[31]=B.[31] FROM #T2 INNER JOIN ( select id,RouteCode,RouteName,BadItem, EXP(sum(log([01])) over(order by id ASC)) AS [01], EXP(sum(log([02])) over(order by id ASC)) AS [02], EXP(sum(log([03])) over(order by id ASC)) AS [03], EXP(sum(log([04])) over(order by id ASC)) AS [04], EXP(sum(log([05])) over(order by id ASC)) AS [05], EXP(sum(log([06])) over(order by id ASC)) AS [06], EXP(sum(log([07])) over(order by id ASC)) AS [07], EXP(sum(log([08])) over(order by id ASC)) AS [08], EXP(sum(log([09])) over(order by id ASC)) AS [09], EXP(sum(log([10])) over(order by id ASC)) AS [10], EXP(sum(log([11])) over(order by id ASC)) AS [11], EXP(sum(log([12])) over(order by id ASC)) AS [12], EXP(sum(log([13])) over(order by id ASC)) AS [13], EXP(sum(log([14])) over(order by id ASC)) AS [14], EXP(sum(log([15])) over(order by id ASC)) AS [15], EXP(sum(log([16])) over(order by id ASC)) AS [16], EXP(sum(log([17])) over(order by id ASC)) AS [17], EXP(sum(log([18])) over(order by id ASC)) AS [18], EXP(sum(log([19])) over(order by id ASC)) AS [19], EXP(sum(log([20])) over(order by id ASC)) AS [20], EXP(sum(log([21])) over(order by id ASC)) AS [21], EXP(sum(log([22])) over(order by id ASC)) AS [22], EXP(sum(log([23])) over(order by id ASC)) AS [23], EXP(sum(log([24])) over(order by id ASC)) AS [24], EXP(sum(log([25])) over(order by id ASC)) AS [25], EXP(sum(log([26])) over(order by id ASC)) AS [26], EXP(sum(log([27])) over(order by id ASC)) AS [27], EXP(sum(log([28])) over(order by id ASC)) AS [28], EXP(sum(log([29])) over(order by id ASC)) AS [29], EXP(sum(log([30])) over(order by id ASC)) AS [30], EXP(sum(log([31])) over(order by id ASC)) AS [31] FROM #ZT ) AS B ON #T2.RouteCode=B.RouteCode AND #T2.BadItem=B.BadItem SELECT * FROM #T1 UNION ALL SELECT RouteCode,RouteName,BadItem, CAST(ISNULL([01],0) AS CHAR(100)) [01],CAST(ISNULL([02],0) AS CHAR(100)) [02],CAST(ISNULL([03],0) AS CHAR(100)) [03],CAST(ISNULL([04],0) AS CHAR(100)) [04],CAST(ISNULL([05],0) AS CHAR(100)) [05],CAST(ISNULL([06],0) AS CHAR(100)) [06],CAST(ISNULL([07],0) AS CHAR(100)) [07],CAST(ISNULL([08],0) AS CHAR(100)) [08], CAST(ISNULL([09],0) AS CHAR(100)) [09],CAST(ISNULL([10],0) AS CHAR(100)) [10],CAST(ISNULL([11],0) AS CHAR(100)) [11],CAST(ISNULL([12],0) AS CHAR(100)) [12],CAST(ISNULL([13],0) AS CHAR(100)) [13],CAST(ISNULL([14],0) AS CHAR(100)) [14],CAST(ISNULL([15],0) AS CHAR(100)) [15],CAST(ISNULL([16],0) AS CHAR(100)) [16], CAST(ISNULL([17],0) AS CHAR(100)) [17],CAST(ISNULL([18],0) AS CHAR(100)) [18],CAST(ISNULL([19],0) AS CHAR(100)) [19],CAST(ISNULL([20],0) AS CHAR(100)) [20],CAST(ISNULL([21],0) AS CHAR(100)) [21],CAST(ISNULL([22],0) AS CHAR(100)) [22],CAST(ISNULL([23],0) AS CHAR(100)) [23],CAST(ISNULL([24],0) AS CHAR(100)) [24], CAST(ISNULL([25],0) AS CHAR(100)) [25],CAST(ISNULL([26],0) AS CHAR(100)) [26],CAST(ISNULL([27],0) AS CHAR(100)) [27],CAST(ISNULL([28],0) AS CHAR(100)) [28],CAST(ISNULL([29],0) AS CHAR(100)) [29],CAST(ISNULL([30],0) AS CHAR(100)) [30],CAST(ISNULL([31],1) AS CHAR(100)) [31] FROM #T2 ORDER BY RouteCode, RouteName,BadItem ASC END
效果：
