下面的计算时间我写的有问题,报错了,所用方法是:
declare @date1 datetime
declare @date2 datetime
select @date1=getdate()
--测试语句
select @date2=getdate()
select datediff(millisecond, @date1, @date2) as 执行时间
--结果是毫秒数
下面的这个句子里,我声明的变量@date1 ,@date2 在使用中出错了,请朋友们帮忙改一下
-- 年级排名和班级排名
declare @sql varchar(4000),@nclassid int ,@nexamid int , @date1 datetime ,@date2 datetime
select @sql='' select @nclassid=32 select @nexamid=17
select @sql=@sql+'max(case scoursename when '''+scoursename+''' then descore else 0 end) '+scoursename+','
from (select distinct scoursename from t_card_course where id in (select ncourseid from t_card_examcou where nclassid =+@nclassid and nexamid=+@nexamid )) a
exec('
select '+@date1+'=(getdate())
select nclassid, name 姓名,'+ @sql+ 'sum(descore) 总分
into #temp
from (
select b.nclassid,b.name,a.descore,c.scoursename from t_card_score a,t_stu b,t_card_course c,t_card_examcou d
where a.nstuid=b.id and a.ncouexamid in
(select id from t_card_examcou where nclassid in (select id from t_card_class where ngradeid =(select ngradeid from t_card_class where id='+@nclassid+' and nschoolid=(select nschoolid from t_card_class where id='+@nclassid+'))) and nexamid='+@nexamid+' )
and a.ncouexamid=d.id and d.ncourseid=c.id group by b.nclassid,b.name,a.descore,c.scoursename )as a
group by name,nclassid
select *,班级名次=(select count(总分) from #temp where 总分>y.总分 and nclassid=y.nclassid)+1 into #tempclass from #temp y order by 班级名次
select *,年级名次 =(select count(总分) from #tempclass where 总分>x.总分)+1 into #tempok from #tempclass x where nclassid ='+@nclassid+' order by 班级名次
alter table #tempok drop column nclassid
select * from #tempok
select '+@date2+'=getdate()
select datediff(millisecond, '+@date1+', '+@date2+')
')
5 个解决方案
#1
exec('
select '+@date1+'=(getdate())
select nclassid, name 姓名,'+ @sql+ 'sum(descore) 总分
into #temp
from (
select b.nclassid,b.name,a.descore,c.scoursename from t_card_score a,t_stu b,t_card_course c,t_card_examcou d
where a.nstuid=b.id and a.ncouexamid in
(select id from t_card_examcou where nclassid in (select id from t_card_class where ngradeid =(select ngradeid from t_card_class where id='+@nclassid+' and nschoolid=(select nschoolid from t_card_class where id='+@nclassid+'))) and nexamid='+@nexamid+' )
and a.ncouexamid=d.id and d.ncourseid=c.id group by b.nclassid,b.name,a.descore,c.scoursename )as a
group by name,nclassid
select *,班级名次=(select count(总分) from #temp where 总分>y.总分 and nclassid=y.nclassid)+1 into #tempclass from #temp y order by 班级名次
select *,年级名次 =(select count(总分) from #tempclass where 总分>x.总分)+1 into #tempok from #tempclass x where nclassid ='+@nclassid+' order by 班级名次
alter table #tempok drop column nclassid
select * from #tempok
select '+@date2+'=getdate()
select datediff(millisecond, '''+@date1+''', '''+@date2+''')
')
#2
如果是2005可以选择包括实际的执行计划,这样就可以知道你的语句哪里用时最多了。
#3
-- 年级排名和班级排名
declare @sql varchar(4000),@nclassid int,@nexamid int,@date1 datetime,@date2 datetime
select @sql='' select @nclassid=38 select @nexamid=19
select @sql=@sql+'max(case scoursename when '''+scoursename+''' then descore else 0 end) '+scoursename+','
from (select distinct scoursename from t_card_course where id in (select ncourseid from t_card_examcou where nclassid =+@nclassid and nexamid=+@nexamid )) a
exec('
select '+@date1+'=(getdate())
select nclassid, name 姓名,'+ @sql+ 'sum(descore) 总分
into #temp
from (
select b.nclassid,b.name,a.descore,c.scoursename from t_card_score a,t_stu b,t_card_course c,t_card_examcou d
where a.nstuid=b.id and a.ncouexamid in
(select id from t_card_examcou where nclassid in (select id from t_card_class where ngradeid =(select ngradeid from t_card_class where id='+@nclassid+' and nschoolid=(select nschoolid from t_card_class where id='+@nclassid+'))) and nexamid='+@nexamid+' )
and a.ncouexamid=d.id and d.ncourseid=c.id group by b.nclassid,b.name,a.descore,c.scoursename )as a
group by name,nclassid
select *,班级名次=(select count(总分) from #temp where 总分>y.总分 and nclassid=y.nclassid)+1 into #tempclass from #temp y order by 班级名次
select *,年级名次 =(select count(总分) from #tempclass where 总分>x.总分)+1 into #tempok from #tempclass x where nclassid ='+@nclassid+' order by 班级名次
alter table #tempok drop column nclassid
select * from #tempok
select '+@date2+'=(getdate())
select datediff(millisecond, '''+@date1+''', '''+@date2+''')
')
按照狙击手的方法我执行了一下,说是两个时间取值时
报错:“‘=’附近出现错误”
#4
顶上去
#5
路过
#1
exec('
select '+@date1+'=(getdate())
select nclassid, name 姓名,'+ @sql+ 'sum(descore) 总分
into #temp
from (
select b.nclassid,b.name,a.descore,c.scoursename from t_card_score a,t_stu b,t_card_course c,t_card_examcou d
where a.nstuid=b.id and a.ncouexamid in
(select id from t_card_examcou where nclassid in (select id from t_card_class where ngradeid =(select ngradeid from t_card_class where id='+@nclassid+' and nschoolid=(select nschoolid from t_card_class where id='+@nclassid+'))) and nexamid='+@nexamid+' )
and a.ncouexamid=d.id and d.ncourseid=c.id group by b.nclassid,b.name,a.descore,c.scoursename )as a
group by name,nclassid
select *,班级名次=(select count(总分) from #temp where 总分>y.总分 and nclassid=y.nclassid)+1 into #tempclass from #temp y order by 班级名次
select *,年级名次 =(select count(总分) from #tempclass where 总分>x.总分)+1 into #tempok from #tempclass x where nclassid ='+@nclassid+' order by 班级名次
alter table #tempok drop column nclassid
select * from #tempok
select '+@date2+'=getdate()
select datediff(millisecond, '''+@date1+''', '''+@date2+''')
')
#2
如果是2005可以选择包括实际的执行计划,这样就可以知道你的语句哪里用时最多了。
#3
-- 年级排名和班级排名
declare @sql varchar(4000),@nclassid int,@nexamid int,@date1 datetime,@date2 datetime
select @sql='' select @nclassid=38 select @nexamid=19
select @sql=@sql+'max(case scoursename when '''+scoursename+''' then descore else 0 end) '+scoursename+','
from (select distinct scoursename from t_card_course where id in (select ncourseid from t_card_examcou where nclassid =+@nclassid and nexamid=+@nexamid )) a
exec('
select '+@date1+'=(getdate())
select nclassid, name 姓名,'+ @sql+ 'sum(descore) 总分
into #temp
from (
select b.nclassid,b.name,a.descore,c.scoursename from t_card_score a,t_stu b,t_card_course c,t_card_examcou d
where a.nstuid=b.id and a.ncouexamid in
(select id from t_card_examcou where nclassid in (select id from t_card_class where ngradeid =(select ngradeid from t_card_class where id='+@nclassid+' and nschoolid=(select nschoolid from t_card_class where id='+@nclassid+'))) and nexamid='+@nexamid+' )
and a.ncouexamid=d.id and d.ncourseid=c.id group by b.nclassid,b.name,a.descore,c.scoursename )as a
group by name,nclassid
select *,班级名次=(select count(总分) from #temp where 总分>y.总分 and nclassid=y.nclassid)+1 into #tempclass from #temp y order by 班级名次
select *,年级名次 =(select count(总分) from #tempclass where 总分>x.总分)+1 into #tempok from #tempclass x where nclassid ='+@nclassid+' order by 班级名次
alter table #tempok drop column nclassid
select * from #tempok
select '+@date2+'=(getdate())
select datediff(millisecond, '''+@date1+''', '''+@date2+''')
')
按照狙击手的方法我执行了一下,说是两个时间取值时
报错:“‘=’附近出现错误”
#4
顶上去
#5
路过