27,580
社区成员
发帖
与我相关
我的任务
分享请大人帮忙看看这段sql有没有替代的方法实现，运行太慢了
这段代码比较耗资源，希望找到一个更好的方法。
居然太长了，再加贴在后面
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_effort_new_summary]
@mDept int,
@mPerson nvarchar(1000),
@mDate1 datetime,
@mDate2 datetime
AS
--说明:
--本存储过程用于new_sum.jsp
--主要用途，产生一较新的统计结果按照规定格式
--可返回一个记录集，仅为Effort投入不为零的记录
--本存储过程需要的参数
--@mDept 部门代号,必须参数.不符合要求则返回空
--@mPerson 人员字符串,非必须参数,如果为空，则不按照人员列表统计 格式： Zhan San,Li Si,Wang Wu
--@mDate1 统计开始时间,必须参数，不符合要求则返回空 格式：2006-06-23
--@mDate2 统计结束时间，必须参数，不符合要求则返回空
--存储过程需要返回的结果集包含如下内容:
--Sort 指出该条记录的类型, 如 项目,设计,EMS项目
--Name 项目或者设计的名字
--HPL/PBA 项目或者设计的负责人或者-
--PCB 设计负责人或者undefined或者-
--Start/EndTime 起止时间或者-
--Person1 @mperson中第一个人在该条记录项目上的投入Effort数值
--Person2 @mperson中第二个人在该条记录项目上的投入Effort数值
--.....
--PersonN @mperson中第N个人在该条记录项目上的投入Effort数值
--不需要提供返回的内容
--sum 由本存储过程计算
--summary 由客户端访问时在服务器端执行，减轻数据库的负担
--如果mPerson为空，则将没有结果
--在程序中判断mDate1 日期小于 mDate2日期,存储过程中不负责判别两时间的大小
/*
*本*号间的内容不是要求内容
--如果mDept为空，则将返回所有Dept记录
--查询所有dept的数据，仅在返回的时候进行筛选
*/
set nocount on
--以下默认参数完整
--declare @sid int,@did int
--declare @count int
--declare @pi int
--set @count=0
--当前用到的
declare @sperson nvarchar(500)
declare @split nvarchar(1)
set @split=','
set @mPerson=rtrim(ltrim(@mPerson))
--declare @sName nvarchar(500)
declare @sHPL nvarchar(500)
declare @sPBA nvarchar(500)
declare @sPCB nvarchar(500)
declare @sTs datetime
declare @sTe datetime
declare @tPerson nvarchar(500)
declare @sOrgType int
--记数
declare @t float
set @t=0
declare @LSum float
set @LSum=0
--定义中间过程中需要执行的sql语句，尤其是需要包含变量的不能直接执行的语句
declare @sql nvarchar(1000)
set @sql=''
--默认临时表
--id 自动编号
--sort 类别
create table #temp (tid int identity(1,1) not null, Sort int, PCBID int, PName nvarchar(1000) not null,HPL nvarchar(1000) not null default('-'),PCB nvarchar(1000) not null default('-'),TS datetime, TE datetime, LSum float not null default(0),hasChild bit not null default(0),childsNum int not null default(0),isDesigning bit not null default(0))
--create table #temp (person nvarchar(100),[AND] float default 0,CCC float default 0,FSD float default 0,OND float default 0,OP float default 0,[R&I] float default 0,WTD float default 0,OtherBD float default 0,CompetenceBuild float default 0,Other float default 0,Holiday float default 0)
--按照人员要求，将人员名作为字段名添加到临时表中
--建立人员临时表
create table #person_temp (pid int identity(1,1) not null,person nvarchar(500),SSum float not null default(0))
while charindex(@split,@mPerson)>0
begin
set @sPerson=rtrim(ltrim(substring(@mPerson,1,charindex(@split,@mPerson)-1)))
set @mPerson=rtrim(ltrim(substring(@mPerson,charindex(@split,@mPerson)+1,len(@mPerson)-charindex(@split,@mPerson))))
print(@sPerson+'----------->')
set @sql='alter table #temp add '+replace(@sPerson,' ','')+' float not null default (0)'
exec(@sql)
insert into #person_temp(person) values(@sPerson)
--update #person_temp set personid=(select id from effortperson where person=@sPerson) where person=@sPerson
--insert into #person_temp(person,personid) values (select person,id from effortperson where person=@sPerson)
end
if @mPerson is not null and len(@mPerson)<>0
begin
print(@mPerson+'----------->')
set @sql='alter table #temp add '+replace(@mPerson,' ','')+' float not null default (0)'
exec(@sql)
insert into #person_temp(person) values(@mPerson)
end
--查询,按照项目
--先PES项目，同时在PES的项目中分支出设计，需要小计
--再EMS项目，同时分支出设计(如果有下级设计),，需要小计
--最后总计，即Sum行
--declare @iProject int
declare @nProject nvarchar(500)
declare @nDesign nvarchar(500)
declare @iPCBid int
--循环中的临时表的tid号
declare @tid int
declare @ttid int
declare @childs int
set @childs=0
declare @isDesigning bit
set @isDesigning=0
declare @t_count int
set @t_count=0
declare cur_project cursor
for
select distinct Project from vEffortReport_Mux where Deptid=@mDept and (datetime between @mDate1 and @mDate2) and person in(select person from #person_temp) group by project order by project
open cur_project
fetch next from cur_project into @nProject
while @@fetch_status=0
begin
--获取Project数据
insert into #temp(PName) values(@nProject)
select top 1 @tid= tid from #temp order by tid desc
--判断是否PES项目还是EMS项目
print('-----------------------------------------')
print ('project:'+@nProject)
select @sOrgType= sum(distinct OrgType) from vEffortReport_Mux where Project=@nProject and Deptid=@mDept
if @sOrgType=2
begin
--PES 项目
--获取HPL信息
print('is PES Project')
declare @stHPL nvarchar(100)
declare cur_hpl cursor
for
--select distinct Project,HPL from veProcessProject_1 group by project,hpl order by project
select distinct HPL from veProcessProject_1 where project=@nProject group by hpl order by hpl
open cur_hpl
fetch next from cur_hpl into @stHPL
while @@fetch_status=0
begin
if @stHPL is not null
begin
if @sHPL is null
set @sHPL=@stHPL
else
set @sHPL=@sHPL+'/'+@stHPL
set @stHPL=null
end
fetch next from cur_hpl into @stHPL
end
close cur_hpl
deallocate cur_hpl
--总结Project的信息
update #temp set sort='1',PName=@nProject, HPL=isnull(@sHPL,'-') where tid=@tid
--小计
set @lSum=0
--select @t=sum(effort) from vEffortReport_Mux where project=@nProject and person in (select person from #person_temp) and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
--对人员循环
declare cur_person cursor
for
select person from #person_temp
open cur_person
fetch next from cur_person into @tPerson
while @@fetch_status=0
begin
set @t=0
select @t=sum(effort) from vEffortReport_Mux where project=@nProject and person=@tPerson and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
set @sql=''
if @t is not null
begin
set @LSum=@LSum+@t
set @sql='update #temp set '+replace(@tPerson,' ','')+'='''+cast(@t as nvarchar(10))+''' where tid='+cast(@tid as nvarchar(10))--ltrim(rtrim(str(@tid)))
exec(@sql)
update #person_temp set SSum=SSum+@t where person=@tPerson
end
set @tPerson=null
fetch next from cur_person into @tPerson
end
close cur_person
deallocate cur_person
update #temp set LSum=@LSum where tid=@tid
set @Lsum=0
--下面是针对Design的信息
--print(@nProject)
--print(@mDept)
--print('select id,design,pba,pb,TimeCreate,closedtag from veProcessProject_1 where project=@nProject and Deptid=@mDept and id in(select OrgID from EMSProject where OrgType=2 and id in(select projectid from vEffortReport_Mux where person in(select person from #person_temp))) order by design')
set @childs=0
declare cur_design cursor
for
select id,design,pba,pb,TimeCreate,closedtag from veProcessProject_1 where project=@nProject and Deptid=@mDept and id in(select OrgID from EMSProject where OrgType=2 and id in(select projectid from vEffortReport_Mux where person in(select person from #person_temp))) order by design
open cur_design
fetch next from cur_design into @iPCBid,@nDesign,@sPBA,@sPCB,@sTs,@sTe
while @@fetch_status=0
begin
print('Design:'+@nDesign)
if @nDesign is not null and @nDesign<>''
begin
--获取PBA,PCB,CreateTime,EndTime信息
--select top 1 project,@sPBA=PBA from veProcessProject_1 where project=@nProject
set @isDesigning=0
set @t_count=0
select @t_count=count(*) from vEffortReport_Mux where Task in('Placement','Modification' ,'Routing') and projectid in(select id from vEmsProject where OrgType=2 and OrgID=@iPCBID)
if @t_count>0
set @isDesigning=1
insert into #temp(PCBID,PName,Sort,HPL,PCB,TS,TE,isDesigning) values(@iPCBid,@nDesign,'2',isnull(@sPBA,'-'),isnull(@sPCB,'-'),@sTs,@sTe,@isDesigning)
select top 1 @ttid= tid from #temp order by tid desc
--小计
--select @t=sum(effort) from vEffortReport_Mux where project=@nProject and design=@nDesign and person in (select person from #person_temp)
--对人员循环
declare cur_person_d cursor
for
select person from #person_temp
open cur_person_d
fetch next from cur_person_d into @tPerson
while @@fetch_status=0
begin
set @t=0
select @t=sum(effort) from vEffortReport_Mux where project=@nProject and design=@nDesign and person=@tPerson and(datetime between @mDate1 and @mDate2) and Deptid=@mDept
set @sql=''
if @t is not null
begin
set @LSum=@LSum+@t
set @sql='update #temp set '+replace(@tPerson,' ','')+'='''+cast(@t as nvarchar(10))+''' where tid='+cast(@ttid as nvarchar(10))--ltrim(rtrim(str(@ttid)))
exec(@sql)
end
set @tPerson=null
fetch next from cur_person_d into @tPerson
end
close cur_person_d
deallocate cur_person_d
--判断Effort值为零
if @LSum is null or @LSum<=0
begin
print('sum <=0')
delete from #temp where tid=@ttid
end
else
begin
print(@LSum)
update #temp set LSum=@LSum where tid=@ttid
set @childs=@childs+1
end
set @Lsum=0
set @ttid=null
--标记该Project下有Design
update #temp set hasChild=1,childsNum=@childs where tid=@tid
end
fetch next from cur_design into @iPCBid,@nDesign,@sPBA,@sPCB,@sTs,@sTe
end
close cur_design
deallocate cur_design
end
if @sOrgType=1
begin
print('\nEMS')
--EMS 项目
--没有HPL信息
--后面的内容在下面的帖子中
--.....
居然太长了，再加贴在后面
...全文
请发表友善的回复…
发表回复
tim_spac 2009-07-08
- 打赏
- 举报
其实行列转换最好是在前台进行
xiciliu 2009-07-06
- 打赏
- 举报
不能用一般的行列转换。
否则我早用了。
这张图片被系统自动截去了一部分。
http://p.blog.csdn.net/images/p_blog_csdn_net/xiciliu/EntryImages/20090703/效果图-effort-monthly-report.gif
这个是完整的。
否则我早用了。
这张图片被系统自动截去了一部分。
http://p.blog.csdn.net/images/p_blog_csdn_net/xiciliu/EntryImages/20090703/效果图-effort-monthly-report.gif
这个是完整的。
tim_spac 2009-07-03
- 打赏
- 举报
[Quote=引用 17 楼 xiciliu 的回复:]
...这个人员啊，项目呀，都是不定的。
需要让系统自动识别，加载。
仍免不了诸多的循环。
...[/Quote]
其实不必将这些不定的值作为字段。而是人员字段的值、项目字段的值，在SQL里GROUP BY，在前台再以适当的方式进行输出。或者完成计算后用另一的段落进行"行列转换"(关于"行列转换"可以在CSDN上找到很多经典的例子)
...这个人员啊，项目呀，都是不定的。
需要让系统自动识别，加载。
仍免不了诸多的循环。
...[/Quote]
其实不必将这些不定的值作为字段。而是人员字段的值、项目字段的值，在SQL里GROUP BY，在前台再以适当的方式进行输出。或者完成计算后用另一的段落进行"行列转换"(关于"行列转换"可以在CSDN上找到很多经典的例子)
大P 2009-07-03
- 打赏
- 举报
天......
xiciliu 2009-07-03
- 打赏
- 举报
tim_spac， 你的方法倒是可以解决部分问题。
但是呀，这个人员啊，项目呀，都是不定的。
需要让系统自动识别，加载。
仍免不了诸多的循环。
来对每个人员在每个项目上的投入时间的统计。
单项统计好后，在进行整体统计。
然后输出成上面的样子。
其实这段代码，在原来的那个服务器上，运行不慢的，只要半分钟。
现在数据库换到另一个奔3的机器上，运行就要至少5分钟，当然也看选择人员多少的情况。
一执行，CPU定被SQL Server占到100%。
但是呀，这个人员啊，项目呀，都是不定的。
需要让系统自动识别，加载。
仍免不了诸多的循环。
来对每个人员在每个项目上的投入时间的统计。
单项统计好后，在进行整体统计。
然后输出成上面的样子。
其实这段代码，在原来的那个服务器上，运行不慢的，只要半分钟。
现在数据库换到另一个奔3的机器上，运行就要至少5分钟，当然也看选择人员多少的情况。
一执行，CPU定被SQL Server占到100%。
xiciliu 2009-07-03
- 打赏
- 举报
这段代码运行出来的结果是：
途中人名 抹掉了。
途中人名 抹掉了。
waikey 2009-06-30
- 打赏
- 举报
这样的过程最好拆分一下，查询的转为视图且使用表变量，不要使用临时表
tim_spac 2009-06-29
- 打赏
- 举报
举一个很白的例子:
declare @arg nvarchar(100)
declare @type nvarchar(100), @amount int
-- 遍历tb表中field字段的各个枚举值
declare cur_o cursor for
select distinct category
from tb
open cur_o
fetch next from cur_o into @arg
while @@fetch_status=0 begin
-- 统计tb2中category的各个type的amount
declare cur_i cursor for
select type, sum(amount)
from tb2
where keyfield = @arg
group by type
open cur_i
fetch next from cur_i into @type, @amount
while @@fetch_status!=-1 begin
update tb3 set
amount = @amount
where category = @arg and type = @type
fetch next from cur_i into @type, @amount
end
close cur_i
deallocate cur_i
fetch next from cur_o into @arg
end
close cur_o
deallocate cur_o
-->
update tb3 set
amount = sum_amount
from tb3 a
join ( select a.category, b.type, sum(amount) as sum_amount
from (select distinct category from tb) as a
join tb2 b on a.category = b.category
group by a.category, b.type
) as b on a.category = b.category and a.type=b.typexiciliu 2009-06-29
- 打赏
- 举报
这段代码当时写得很累，主要是为了偷懒，就用了游标，而且都写在一个存储过程中。
tim_spac 说的 "将游标嵌套改为表关联的update" 不明白，能具体点吗?
tim_spac 说的 "将游标嵌套改为表关联的update" 不明白，能具体点吗?
greenery 2009-06-29
- 打赏
- 举报
好长。。。。。。。
看不下去了，你说重点吧。
看不下去了，你说重点吧。
tim_spac 2009-06-26
- 打赏
- 举报
简化方式:
1.可以考虑首先拆分为若干个存储过程，
2.再考虑公共方法提取为存储过程或函数
3.再考虑将游标嵌套改为表关联的update
第三步带来的效益将是非常可观的
1.可以考虑首先拆分为若干个存储过程，
2.再考虑公共方法提取为存储过程或函数
3.再考虑将游标嵌套改为表关联的update
第三步带来的效益将是非常可观的
feixianxxx 2009-06-26
- 打赏
- 举报
帮顶
xiequan2 2009-06-26
- 打赏
- 举报
帮顶！
xiciliu 2009-06-26
- 打赏
- 举报
主要是通过两个视图将众多表结合起来了。
视图vEffortReport_Mux
视图 veProcessProject_1
同一个Project下可能有多个design。
视图vEffortReport_Mux
id datetime effort comments person dept project design task addtime overtime projectid deptid personid taskid typesort orgtype orgid
85 2006-3-7 0:00:00 2 about EC and NV Zhao Aihua AND CTCP-A CTCP-A Tech.Support 2006-3-7 16:54:35 False 16 1 8 13 1 1 29
86 2006-3-7 0:00:00 6 project interface for TSS15/TCONGI and IPDA;clean up the design record Zhao Aihua NULL Other NULL Management 2006-3-7 16:54:35 False 21 NULL 8 16 2 1 34
87 2006-3-7 0:00:00 5 OND 8XFEE Routing Shui Wenfeng OND TSS15 8XFEE Routing 2006-3-7 17:03:20 False 1 5 7 5 1 1 14
视图 veProcessProject_1
id project design dept pba pb hpl timecreate deptid closedtag
200708063 1642EM R3.1 ISA ES4 GE MB OND Zhu Hong Cao Ming shen yonghong 2007-8-29 15:06:40 5 2008-2-20 10:59:00
200801011 1642EM R3.1 main baord (third time) OND shen yonghong Wang Hui Q shen yonghong 2008-1-16 11:40:44 5 2008-1-21 15:54:00
200705026 1642EM R3.1 main board OND shen yonghong Wang Hui Q shen yonghong 2007-5-9 16:16:56 5 2007-8-14 15:30:00
同一个Project下可能有多个design。
ai_li7758521 2009-06-26
- 打赏
- 举报
太长了，帮顶
xiciliu 2009-06-26
- 打赏
- 举报
看到里面嵌套了多个 游标吗？
好像是这些东西运行起来，很慢的，而且比较耗运行Sql Server的服务器的cpu资源。
针对这个应用，有没有减轻资源负担的办法。
好像是这些东西运行起来，很慢的，而且比较耗运行Sql Server的服务器的cpu资源。
针对这个应用，有没有减轻资源负担的办法。
xiciliu 2009-06-26
- 打赏
- 举报
就这么多了。
好像比较多，：））
好像比较多，：））
xuejie09242 2009-06-26
- 打赏
- 举报
能直接说下需求和数据表结构涉及的主要东西，看这么长的过程，没有解释，太难了，呵呵。
xiciliu 2009-06-26
- 打赏
- 举报
-----别急哈，后面还有呢。
----接上面
if @sOrgType=3
begin
--PES 项目
--获取HPL信息
print('is PES Project')
--declare @stHPL nvarchar(100)
declare cur_hpl cursor
for
--select distinct Project,HPL from veProcessProject_1 group by project,hpl order by project
select distinct HPL from veProcessProject_1 where project=@nProject group by hpl order by hpl
open cur_hpl
fetch next from cur_hpl into @stHPL
while @@fetch_status=0
begin
if @stHPL is not null
begin
if @sHPL is null
set @sHPL=@stHPL
else
set @sHPL=@sHPL+'/'+@stHPL
set @stHPL=null
end
fetch next from cur_hpl into @stHPL
end
close cur_hpl
deallocate cur_hpl
--总结Project的信息
update #temp set sort='1',PName=@nProject, HPL=isnull(@sHPL,'-') where tid=@tid
--小计
set @lSum=0
--select @t=sum(effort) from vEffortReport_Mux where project=@nProject and person in (select person from #person_temp) and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
--对人员循环
declare cur_person cursor
for
select person from #person_temp
open cur_person
fetch next from cur_person into @tPerson
while @@fetch_status=0
begin
set @t=0
select @t=sum(effort) from vEffortReport_Mux where project=@nProject and person=@tPerson and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
set @sql=''
if @t is not null
begin
set @LSum=@LSum+@t
set @sql='update #temp set '+replace(@tPerson,' ','')+'='''+cast(@t as nvarchar(10))+''' where tid='+cast(@tid as nvarchar(10))--ltrim(rtrim(str(@tid)))
exec(@sql)
update #person_temp set SSum=SSum+@t where person=@tPerson
end
set @tPerson=null
fetch next from cur_person into @tPerson
end
close cur_person
deallocate cur_person
update #temp set LSum=@LSum where tid=@tid
set @Lsum=0
--对Design
--first to PES
set @childs=0
declare cur_design cursor
for
select id,design,pba,pb,TimeCreate,closedtag from veProcessProject_1 where project=@nProject and Deptid=@mDept and id in(select OrgID from EMSProject where OrgType=2 and id in(select projectid from vEffortReport_Mux where person in(select person from #person_temp))) order by design
open cur_design
fetch next from cur_design into @iPCBid,@nDesign,@sPBA,@sPCB,@sTs,@sTe
while @@fetch_status=0
begin
print('Design:'+@nDesign)
if @nDesign is not null and @nDesign<>''
begin
--获取PBA,PCB,CreateTime,EndTime信息
--select top 1 project,@sPBA=PBA from veProcessProject_1 where project=@nProject
set @isDesigning=0
set @t_count=0
select @t_count=count(*) from vEffortReport_Mux where Task in('Placement','Modification' ,'Routing') and projectid in(select id from vEmsProject where OrgType=2 and OrgID=@iPCBID)
if @t_count>0
set @isDesigning=1
insert into #temp(PCBID,PName,Sort,HPL,PCB,TS,TE,isDesigning) values(@iPCBid,@nDesign,'2',isnull(@sPBA,'-'),isnull(@sPCB,'-'),@sTs,@sTe,@isDesigning)
select top 1 @ttid= tid from #temp order by tid desc
--小计
--select @t=sum(effort) from vEffortReport_Mux where project=@nProject and design=@nDesign and person in (select person from #person_temp)
--对人员循环
declare cur_person_d cursor
for
select person from #person_temp
open cur_person_d
fetch next from cur_person_d into @tPerson
while @@fetch_status=0
begin
set @t=0
select @t=sum(effort) from vEffortReport_Mux where project=@nProject and design=@nDesign and person=@tPerson and(datetime between @mDate1 and @mDate2) and Deptid=@mDept
set @sql=''
if @t is not null
begin
set @LSum=@LSum+@t
set @sql='update #temp set '+replace(@tPerson,' ','')+'='''+cast(@t as nvarchar(10))+''' where tid='+cast(@ttid as nvarchar(10))--ltrim(rtrim(str(@ttid)))
exec(@sql)
end
set @tPerson=null
fetch next from cur_person_d into @tPerson
end
close cur_person_d
deallocate cur_person_d
--判断Effort值为零
if @LSum is null or @LSum<=0
begin
print('sum <=0')
delete from #temp where tid=@ttid
end
else
begin
print(@LSum)
update #temp set LSum=@LSum where tid=@ttid
set @childs=@childs+1
end
set @Lsum=0
set @ttid=null
--标记该Project下有Design
update #temp set hasChild=1,childsNum=@childs where tid=@tid
end
fetch next from cur_design into @iPCBid,@nDesign,@sPBA,@sPCB,@sTs,@sTe
end
close cur_design
deallocate cur_design
--second to EMS
set @nDesign=null
--set @childs=0
--set @ipcbid=0
declare cur_design cursor
for
select distinct design from vEffortReport_Mux where project=@nProject and OrgType=1 and (datetime between @mDate1 and @mDate2) and Deptid=@mDept and person in(select person from #person_temp) order by design
open cur_design
fetch next from cur_design into @nDesign
while @@fetch_status=0
begin
--获取PBA,PCB,CreateTime,EndTime信息
--select top 1 project,@sPBA=PBA from veProcessProject_1 where project=@nProject
print('Design:'+@nDesign)
if @nDesign is not null and @nDesign<>''
begin
select @ipcbid=id from project where design like '%'+@nDesign+'%' and projectname like '%'+@nProject+'%' --and (datetime between @mDate1 and @mDate2) and Deptid=@mDept and person in(select person from #person_temp)
set @t_count=0
set @isDesigning=0
select @t_count=count(*) from vEffortReport_Mux where Task in('Placement','Modification' ,'Routing') and project=@nProject and design=@nDesign and OrgType=1 and (datetime between @mDate1 and @mDate2) and Deptid=@mDept and person in(select person from #person_temp)
if @t_count>0
set @isDesigning=1
insert into #temp(PCBID,PName,Sort,isDesigning) values(@ipcbid,@nDesign,'2',@isDesigning)
select top 1 @ttid= tid from #temp order by tid desc
--小计
--select @t=sum(effort) from vEffortReport_Mux where project=@nProject and design=@nDesign and person in (select person from #person_temp) and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
--对人员循环
declare cur_person_d cursor
for
select person from #person_temp
open cur_person_d
fetch next from cur_person_d into @tPerson
while @@fetch_status=0
begin
set @t=0
select @t=sum(effort) from vEffortReport_Mux where project=@nProject and design=@nDesign and person=@tPerson and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
set @sql=''
if @t is not null
begin
set @LSum=@LSum+@t
set @sql='update #temp set '+replace(@tPerson,' ','')+'='''+cast(@t as nvarchar(10))+''' where tid='+cast(@ttid as nvarchar(10))--ltrim(rtrim(str(@ttid)))
exec(@sql)
end
set @tPerson=null
fetch next from cur_person_d into @tPerson
end
close cur_person_d
deallocate cur_person_d
--判断Effort值为零
if @LSum is null or @LSum<=0
begin
print('sum <=0')
delete from #temp where tid=@ttid
end
else
begin
print(@LSum)
update #temp set LSum=@LSum where tid=@ttid
set @childs=@childs+1
end
set @Lsum=0
set @ttid=null
--标记该Project下有Design
update #temp set hasChild=1,childsNum=@childs where tid=@tid
end
fetch next from cur_design into @nDesign
end
close cur_design
deallocate cur_design
end
--select @t=sum(Effort) from vEffortReport_Mux
--select @t=sum(effort) from pereffort where person=@pi and (datetime between @mDate1 and @mDate2) and project in (select vEMSProject.id from vEMSProject left join department on vEMSProject.dept=department.id where department.dept='AND')
--清空循环中的临时变量
set @tid=null
set @nProject=null
set @sHPL=null
fetch next from cur_project into @nProject
end
close cur_project
deallocate cur_project
--添加总计
declare @stol float
set @stol=0
set @LSum=0
insert into #temp(sort,PName) values(10,'Sum')
select top 1 @tid= tid from #temp order by tid desc
declare cur_person cursor
for
select person,SSum from #person_temp
open cur_person
fetch next from cur_person into @tPerson,@stol
while @@fetch_status=0
begin
if @stol is not null
begin
--print('>>>>>>>>>>>>>>>>>>>>>')
--print('abc: '+cast(@stol as nvarchar(10)))
set @LSum=@LSum+@stol
set @sql='update #temp set '+replace(@tPerson,' ','')+'='''+cast(@stol as nvarchar(10))+''' where tid='+cast(@tid as nvarchar(10)) --ltrim(rtrim(str(@tid)))
exec(@sql)
end
set @tPerson=null
fetch next from cur_person into @tPerson,@stol
end
close cur_person
deallocate cur_person
update #temp set LSum=@LSum where tid=@tid
set @tid=null
set nocount off
select * from #temp
drop table #temp
drop table #person_temp
xiciliu 2009-06-26
- 打赏
- 举报
--接上面的内容
--总结Project的信息
update #temp set sort='1',PName=@nProject where tid=@tid
--小计
set @lSum=0
--select @t=sum(effort) from vEffortReport_Mux where project=@nProject and person in (select person from #person_temp) and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
--对人员循环
declare cur_person cursor
for
select person from #person_temp
open cur_person
fetch next from cur_person into @tPerson
while @@fetch_status=0
begin
set @t=0
select @t=sum(effort) from vEffortReport_Mux where project=@nProject and person=@tPerson and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
set @sql=''
if @t is not null
begin
set @LSum=@LSum+@t
set @sql='update #temp set '+replace(@tPerson,' ','')+'='''+cast(@t as nvarchar(10))+''' where tid='+cast(@tid as nvarchar(10))--ltrim(rtrim(str(@tid)))
exec(@sql)
update #person_temp set SSum=SSum+@t where person=@tPerson
end
set @tPerson=null
fetch next from cur_person into @tPerson
end
close cur_person
deallocate cur_person
update #temp set LSum=@LSum where tid=@tid
set @Lsum=0
--下面是针对Design的信息
set @nDesign=null
set @childs=0
set @ipcbid=0
declare cur_design cursor
for
select distinct design from vEffortReport_Mux where project=@nProject and OrgType=1 and (datetime between @mDate1 and @mDate2) and Deptid=@mDept and person in(select person from #person_temp) order by design
open cur_design
fetch next from cur_design into @nDesign
while @@fetch_status=0
begin
--获取PBA,PCB,CreateTime,EndTime信息
--select top 1 project,@sPBA=PBA from veProcessProject_1 where project=@nProject
print('Design:'+@nDesign)
if @nDesign is not null and @nDesign<>''
begin
select @ipcbid=id from project where design like '%'+@nDesign+'%' and projectname like '%'+@nProject+'%' --and (datetime between @mDate1 and @mDate2) and Deptid=@mDept and person in(select person from #person_temp)
set @t_count=0
set @isDesigning=0
select @t_count=count(*) from vEffortReport_Mux where Task in('Placement','Modification' ,'Routing') and project=@nProject and design=@nDesign and OrgType=1 and (datetime between @mDate1 and @mDate2) and Deptid=@mDept and person in(select person from #person_temp)
if @t_count>0
set @isDesigning=1
insert into #temp(PCBID,PName,Sort,isDesigning) values(@ipcbid,@nDesign,'2',@isDesigning)
select top 1 @ttid= tid from #temp order by tid desc
--小计
--select @t=sum(effort) from vEffortReport_Mux where project=@nProject and design=@nDesign and person in (select person from #person_temp) and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
--对人员循环
declare cur_person_d cursor
for
select person from #person_temp
open cur_person_d
fetch next from cur_person_d into @tPerson
while @@fetch_status=0
begin
set @t=0
select @t=sum(effort) from vEffortReport_Mux where project=@nProject and design=@nDesign and person=@tPerson and (datetime between @mDate1 and @mDate2) and Deptid=@mDept
set @sql=''
if @t is not null
begin
set @LSum=@LSum+@t
set @sql='update #temp set '+replace(@tPerson,' ','')+'='''+cast(@t as nvarchar(10))+''' where tid='+cast(@ttid as nvarchar(10))--ltrim(rtrim(str(@ttid)))
exec(@sql)
end
set @tPerson=null
fetch next from cur_person_d into @tPerson
end
close cur_person_d
deallocate cur_person_d
--判断删除Effort值为零的记录
if @LSum is null or @LSum<=0
begin
print('sum less then 0')
delete from #temp where tid=@ttid
end
else
begin
print(@LSum)
update #temp set LSum=@LSum where tid=@ttid
set @childs=@childs+1
end
set @Lsum=0
set @ttid=null
--标记该Project下有Design
update #temp set hasChild=1,childsNum=@childs where tid=@tid
end
fetch next from cur_design into @nDesign
end
close cur_design
deallocate cur_design
end
-----还是长了，再后面有
--......
加载更多回复（1）
