不保证有效,仅供建议。而且这个的效果等于重启机器,所以不要在正式客户环境下使用 ------其他解决方案-------------------- 类似这种问题,不要盯着sql,去从语句本身找问题,应该从环境找原因。
你确认你的执行环境完全相同? ------其他解决方案-------------------- 是不是我重启数据库就没问题么? ------其他解决方案-------------------- 你可以 尝试多种方式,由于这些问题的确是挺奇怪。所以一般只能猜测。 ------其他解决方案-------------------- 重启不行啊!不行啊 ------其他解决方案-------------------- 两个语句的执行计划贴出来看看 ------其他解决方案-------------------- ALTER PROCEDURE [dbo].[UVYYKQ_YMJJ]
@YM varchar(20),
@KQKS varchar(20)
AS
begin
Declare @OYM varchar(20)
select @OYM=CONVERT(varchar(6),
DATEADD(Month,-1,CAST((SUBSTRING(@YM,0,5)+'-'+SUBSTRING(@YM,5,7) +'-01') as datetime)), 112)
--计算的人员条件
insert YYMonthHL(A0188,YM)
select distinct(A0188),CONVERT(varchar(6), YYKQData.KQDate, 112)
from YYKQData where CONVERT(varchar(6), YYKQData.KQDate, 112) = @YM and
YYKQData.A0188 not in (select A0188 from YYMonthHL where YM=@YM)
and YYKQData.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
update YYMonthHL set JJTS=0,BYBJ=0,BYGZ=0,BYYB=0,BYYJX=0,BYYX=0
where YM=@YM and A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--计算本月应休
update YYMonthHL set BYYX=round(cast(A.HZ as numeric(10,1))/2,1)
from (select
SUM( case when [am] in (78,87) then 1 else 0 end)+
SUM( case when [pm] in (78,87) then 1 else 0 end)+
SUM( case when [nt] in (78,87) then 1 else 0 end)+
0 as HZ,@YM YM from YYMB
where month=SUBSTRING(@YM,0,5)+'-'+SUBSTRING(@YM,5,7) and mode=1 group by month ) A
where A.YM=YYMonthHL.YM and A.YM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--计算已经休息
update YYMonthHL set BYYJX=A.XX
from
(
select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,round(cast(COUNT(KQ) as numeric(10,1))/2,1) as XX
from ZSYY_KQ_HZ
where KQ in(select ID from YyZD where XX=1 )
group by (A0188),CONVERT(varchar(6), KQDate, 112)