set
ANSI_NULLS
ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <获取各车队申请用车情况,按车队分组(指定时间片内)>
-- =============================================
ALTER PROCEDURE [ dbo ] . [ procReportCarNeedsAnalysisDept ]
@fromdate DateTime ,
@todate DateTime
AS
BEGIN
-- set nocount on;
declare @dID varchar ( 50 ), @pID varchar ( 50 ), @num int
set @num = 0
-- 保存数据的临时表
CREATE TABLE #me(TdID varchar ( 50 ),TpID varchar ( 50 ),Tname varchar ( 50 ),Tnum int ,TdepLevel varchar ( 50 ))
-- 各部门用车统计 注意null不可操作,要转为0(使用函数isnull(value,0))
insert into #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 INNER JOIN
dr_Paral AS b ON a.dispatchID = b.dispatchID -- and dispatchDate between @fromdate and @todate
INNER JOIN
carReq AS c ON b.reqID = c.reqID
)
AS tmp1
GROUP BY deptID) AS tmp2
RIGHT OUTER JOIN uapDept AS d ON tmp2.deptID = d.departmentID
ORDER BY
d.departmentLevel desc
declare curTest cursor for ( select TdID,TpID,Tnum from #me )
-- 按上级部门从低向上累加( 7-> 6->5 .... ->2)注意上一个表是按部门级别排序
open curTest
FETCH NEXT FROM curTest INTO @dID , @pID , @num
while @@FETCH_STATUS = 0
Begin
update #me set Tnum = Tnum + @num
where TdID = @pID
FETCH NEXT FROM curTest INTO @dID , @pID , @num
End
close CurTest
DEALLOCATE CurTest
-- 显示4级部门名与用车数量
Select Tname,Tnum from #me where TdepLevel = 4 and TpID = 6209
-- drop table #me
END
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <获取各车队申请用车情况,按车队分组(指定时间片内)>
-- =============================================
ALTER PROCEDURE [ dbo ] . [ procReportCarNeedsAnalysisDept ]
@fromdate DateTime ,
@todate DateTime
AS
BEGIN
-- set nocount on;
declare @dID varchar ( 50 ), @pID varchar ( 50 ), @num int
set @num = 0
-- 保存数据的临时表
CREATE TABLE #me(TdID varchar ( 50 ),TpID varchar ( 50 ),Tname varchar ( 50 ),Tnum int ,TdepLevel varchar ( 50 ))
-- 各部门用车统计 注意null不可操作,要转为0(使用函数isnull(value,0))
insert into #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 INNER JOIN
dr_Paral AS b ON a.dispatchID = b.dispatchID -- and dispatchDate between @fromdate and @todate
INNER JOIN
carReq AS c ON b.reqID = c.reqID
)
AS tmp1
GROUP BY deptID) AS tmp2
RIGHT OUTER JOIN uapDept AS d ON tmp2.deptID = d.departmentID
ORDER BY
d.departmentLevel desc
declare curTest cursor for ( select TdID,TpID,Tnum from #me )
-- 按上级部门从低向上累加( 7-> 6->5 .... ->2)注意上一个表是按部门级别排序
open curTest
FETCH NEXT FROM curTest INTO @dID , @pID , @num
while @@FETCH_STATUS = 0
Begin
update #me set Tnum = Tnum + @num
where TdID = @pID
FETCH NEXT FROM curTest INTO @dID , @pID , @num
End
close CurTest
DEALLOCATE CurTest
-- 显示4级部门名与用车数量
Select Tname,Tnum from #me where TdepLevel = 4 and TpID = 6209
-- drop table #me
END
公司里实习写的一个存储过程,复习太多数SQL语句:) 说起来也不复杂,只是不知道能否优化一下哈!!
用到了虚表,游标.
1.虚表里有一个用法 insert into 后可直接跟select集
2.isnull(name,defaultvalue),如果这个字段为null的话就付上默认值,因为null不用相加,所以一定要处理一下
3.用到游标肯定是有循环咯
4.right joint .. on ..匹配所有项,即使无数据也付上null值
5.程序是统计各部门用车数量,而每个部门又有子部门父部门,第个部门的用车数是本部门用车量加上所有子部门的用车量,所以要根据本部门的用车量;加到父部门里,从低级向高级加