set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <获取各车队申请用车情况,按车队分组(指定时间片内)> -- ============================================= ALTERPROCEDURE[dbo].[procReportCarNeedsAnalysisDept] @fromdateDateTime, @todateDateTime AS BEGIN --set nocount on; declare@dIDvarchar(50),@pIDvarchar(50),@numint set@num=0 --保存数据的临时表 CREATETABLE #me(TdID varchar(50),TpID varchar(50),Tname varchar(50),Tnum int,TdepLevel varchar(50)) --各部门用车统计 注意null不可操作,要转为0(使用函数isnull(value,0)) insertinto #me(TdID,TpID,Tname,Tnum,TdepLevel) SELECT d.departmentID,d.parentDepartID,d.departmentName, isnull(tmp2.num,0),d.departmentLevel FROM (SELECT deptID, COUNT(deptID) AS num FROM (SELECT a.dispatchID, b.reqID, c.deptID FROM carDispatch AS a INNERJOIN dr_Paral AS b ON a.dispatchID = b.dispatchID --and dispatchDate between @fromdate and @todate INNERJOIN carReq AS c ON b.reqID = c.reqID ) AS tmp1 GROUPBY deptID) AS tmp2 RIGHTOUTERJOIN uapDept AS d ON tmp2.deptID = d.departmentID ORDERBY d.departmentLevel desc declare curTest cursorfor ( select TdID,TpID,Tnum from #me ) --按上级部门从低向上累加( 7-> 6->5 .... ->2)注意上一个表是按部门级别排序 open curTest FETCHNEXTFROM curTest INTO@dID,@pID,@num while@@FETCH_STATUS=0 Begin update #me set Tnum = Tnum +@num where TdID =@pID FETCHNEXTFROM curTest INTO@dID ,@pID,@num End close CurTest DEALLOCATE CurTest --显示4级部门名与用车数量 Select Tname,Tnum from #me where TdepLevel =4and TpID =6209 --drop table #me END