| --EXEC [dbo].[Sp_GetTopSrap] 6,2,40 CREATE PROCEDURE [dbo].[Sp_GetTopSrap]--取得报废 @plantID VARCHAR(3)='6',--工厂ID @Type INT=1,--1表示按责任部门；2表示安装报废原因 @TopNumber INT=20--前名次 AS BEGIN DECLARE @SQL VARCHAR(8000) IF @Type=1 SET @SQL=' IF OBJECT_ID(''tempdb..#Tmp'') IS NOT NULL DROP TABLE #Tmp SELECT IDENTITY(INT,1,1) AS ID,责任部门,SUM(报废面积) AS 报废面积 ,CAST(0 AS NUMERIC(18,2)) AS 比率 INTO #Tmp FROM ERPScrapt WHERE RKEY15='+CAST(@plantID AS VARCHAR(2))+' GROUP BY 责任部门,RKEY15 ORDER BY SUM(报废面积) DESC UPDATE A SET A.比率=B.比率/dbo.GetSumSrapt('+CAST(@plantID AS VARCHAR(2))+') FROM #Tmp A, (SELECT B1.ID,ISNULL(SUM(B2.报废面积),0) AS 比率 FROM #Tmp B1,#Tmp B2 WHERE B1.ID>=B2.ID GROUP BY B1.ID) B WHERE A.ID=B.ID SELECT TOP '+CAST(@TopNumber AS VARCHAR(3))+' 责任部门,报废面积, 比率 FROM #Tmp ORDER BY ID' IF @Type=2 SET @SQL=' IF OBJECT_ID(''tempdb..#Tmp'') IS NOT NULL DROP TABLE #Tmp SELECT IDENTITY(INT,1,1) AS ID,报废原因,SUM(报废面积) AS 报废面积 ,CAST(0 AS NUMERIC(18,2)) AS 比率 INTO #Tmp FROM ERPScrapt WHERE RKEY15='+CAST(@plantID AS VARCHAR(2))+' GROUP BY 报废原因,RKEY15 ORDER BY SUM(报废面积) DESC UPDATE A SET A.比率=B.比率/dbo.GetSumSrapt('+CAST(@plantID AS VARCHAR(2))+') FROM #Tmp A, (SELECT B1.ID,ISNULL(SUM(B2.报废面积),0) AS 比率 FROM #Tmp B1,#Tmp B2 WHERE B1.ID>=B2.ID GROUP BY B1.ID) B WHERE A.ID=B.ID SELECT TOP '+CAST(@TopNumber AS VARCHAR(3))+' 报废原因,报废面积, 比率 FROM #Tmp ORDER BY ID ' EXEC(@SQL) END D:\diaoyong.jpg |
