我做了一个存储结构，结果运行的时候提示 派生表 不可更新,因为派生表中的某一列是派生的或是常量
程序如下ALTER PROCEDURE dbo.月考勤计算1
(@ny char(4))
AS update b
set b.kkg = case when b.d>=240 then 0.5 else 0 end + case when b.t>=240 then 0.5 else 0 end,
b.ksj = case when b.d>15 and b.d< 240 then 0.25 else 0 end + case when b.t>15 and b.t <240 then 0.5 else 0 end
from (
select a.kkg,a.ksj,datediff(mi,a.rq,a.sd)-datediff(mi,0,a.yd) as d,
datediff(mi,0,yt)-datediff(mi,a.rq,a.st) + case when a.blb = 3 then 1440 else 0 end as t
from 考勤表 a
where a.ny = @ny and a.sd is not null and a.kzz = 0
) b如果直接用考勤表来更新的话，是没有问题，不过涉及到两个计算公式的重复使用，感觉有些繁琐，所以想使用派生表来简化下
不知道怎么更改下会比较有效率点，谢谢
程序如下ALTER PROCEDURE dbo.月考勤计算1
(@ny char(4))
AS update b
set b.kkg = case when b.d>=240 then 0.5 else 0 end + case when b.t>=240 then 0.5 else 0 end,
b.ksj = case when b.d>15 and b.d< 240 then 0.25 else 0 end + case when b.t>15 and b.t <240 then 0.5 else 0 end
from (
select a.kkg,a.ksj,datediff(mi,a.rq,a.sd)-datediff(mi,0,a.yd) as d,
datediff(mi,0,yt)-datediff(mi,a.rq,a.st) + case when a.blb = 3 then 1440 else 0 end as t
from 考勤表 a
where a.ny = @ny and a.sd is not null and a.kzz = 0
) b如果直接用考勤表来更新的话，是没有问题，不过涉及到两个计算公式的重复使用，感觉有些繁琐，所以想使用派生表来简化下
不知道怎么更改下会比较有效率点，谢谢
ny xm rq blb yd sd yt st kzz kgg ksj
1203 白得永 23-Mar-12 3 23:00:00 2012-3-23 22:35 7:00:00 2012-3-24 7:05 1 0 0
1203 白易 27-Feb-12 0 8:30:00 2012-2-27 8:23 17:30:00 2012-2-27 17:32 1 0 0
select a.kkg,a.ksj,datediff(mi,a.rq,a.sd)-datediff(mi,0,a.yd) as d,
datediff(mi,0,yt)-datediff(mi,a.rq,a.st) + case when a.blb = 3 then 1440 else 0 end as t
from 考勤表 a
where a.ny = @ny and a.sd is not null and a.kzz = 0update b
set b.kkg = case when b.d>=240 and b.t>=240 then 1 else 0 end ,
b.ksj = case when (b.d>15 and b.d< 240) and (b.t>15 and b.t <240) then 0.75 else 0 end
from #test