-------------------------------------------------------------------------UPDATE t_PANewData SET FPA1109=(case when t_PA_Personal.F_110>t_PA_Personal.F_112 and t_PA_Personal.F_110>t_PA_Personal.F_116 then t_PA_Personal.F_110 when t_PA_Personal.F_112>t_PA_Personal.F_110 and t_PA_Personal.F_112>t_PA_Personal.F_116 then t_PA_Personal.F_112 else t_PA_Personal.F_116 end) from t_PANewData inner join t_PA_Personal on t_PANewData.FEmpID=t_PA_Personal.FItemID LEFT OUTER JOIN t_PA_ItemRef ON t_PA_Personal.FEmpGroupID = t_PA_ItemRef.FItemRefID Where FYear='2011' AND FPeriod='12' AND t_PA_ItemRef.Fname = '正式员工'我是这样写的,怎么加条件呢? ----------------------------------------------------------------------------------------- UPDATE t_PANewData SET FPA1109=(case when isnull(t_PA_Personal.F_110,0)>isnull(t_PA_Personal.F_112,0) and isnull(t_PA_Personal.F_110,0)>isnull(t_PA_Personal.F_116,0) then isnull(t_PA_Personal.F_110,0) when isnull(t_PA_Personal.F_112,0)>isnull(t_PA_Personal.F_110,0) and isnull(t_PA_Personal.F_112,0)>isnull(t_PA_Personal.F_116,0) then isnull(t_PA_Personal.F_112,0) else isnull(t_PA_Personal.F_116,0) end) from t_PANewData inner join t_PA_Personal on t_PANewData.FEmpID=t_PA_Personal.FItemID LEFT OUTER JOIN t_PA_ItemRef ON t_PA_Personal.FEmpGroupID = t_PA_ItemRef.FItemRefID Where FYear='2011' AND FPeriod='12' AND t_PA_ItemRef.Fname = '正式员工'这样写对吗? 最后的ELSE isnull(t_PA_Personal.F_116,0) end 对吗? -------------------------------------------------------------
这样的话,null值就当0来比较了.
-----------------------------------------------------------------------------------------
UPDATE t_PANewData SET FPA1109=(case when isnull(t_PA_Personal.F_110,0)>isnull(t_PA_Personal.F_112,0) and isnull(t_PA_Personal.F_110,0)>isnull(t_PA_Personal.F_116,0) then isnull(t_PA_Personal.F_110,0) when isnull(t_PA_Personal.F_112,0)>isnull(t_PA_Personal.F_110,0) and isnull(t_PA_Personal.F_112,0)>isnull(t_PA_Personal.F_116,0) then isnull(t_PA_Personal.F_112,0) else isnull(t_PA_Personal.F_116,0) end) from t_PANewData inner join t_PA_Personal on t_PANewData.FEmpID=t_PA_Personal.FItemID LEFT OUTER JOIN t_PA_ItemRef ON t_PA_Personal.FEmpGroupID = t_PA_ItemRef.FItemRefID Where FYear='2011' AND FPeriod='12' AND t_PA_ItemRef.Fname = '正式员工'这样写对吗? 最后的ELSE isnull(t_PA_Personal.F_116,0) end 对吗?
-------------------------------------------------------------
null is null如果要让null =null 可以用isnull函数。