用复杂条件从一个表更新另一个表

我想这会成功的。我使用is null并交换每个isnull中列的顺序,这样,如果一对中只有一列为空,那么计算实际上变成(X+X)/2,也就是X。换句话说,如果一对中只有一列为空,则返回非空值。如果两者都为空,则整个计算将返回空。

;with cteTemp as (
    select keycol,
           (isnull(ColA, ColB) + isnull(ColB, ColA))/2 as TempA,
           (isnull(ColC, ColD) + isnull(ColD, ColC))/2 as TempB
        from TableA
)
update t2
    set AvgCol = (isnull(t1.TempA, t1.TempB) + isnull(t1.TempB, t1.TempA))/2
    from cteTemp t1
        inner join Table2 t2
            on t1.keycol = t2.keycol

使用 COALESCE 独占可以大大减少代码开销:

UPDATE t2
SET AvgCol = COALESCE( (tempA + tempB) / 2, tempA, tempB)
FROM @table2 t2 
INNER JOIN (
  SELECT
    KeyCol, 
    COALESCE( (colA + colB) / 2, colA, colB) AS tempA,
    COALESCE( (colC + colD) / 2, colC, colD) AS tempB
  FROM @tableA
) tA ON t2.KeyCol = tA.KeyCol

SELECT Table2.keycol AS KEY
       CASE
              WHEN colA   IS NULL
              AND    colb IS NULL
              THEN NULL
              WHEN colA   IS NOT NULL
              AND    colb IS NOT NULL
              THEN (colA + colB)/2
              ELSE COALESCE(colA,colB)
       END AS TempA,
       CASE
              WHEN colC   IS NULL
              AND    cold IS NULL
              THEN NULL
              WHEN colC   IS NOT NULL
              AND    cold IS NOT NULL
              THEN (colC + cold)/2
              ELSE COALESCE(colc,colc)
       END AS Tempb
FROM
ON (
              Table2.keycol = Table1.keycol
       )
UPDATE Table2
SET    AVG =
       CASE
              WHEN TempA   IS NULL
              AND    Tempb IS NULL
              THEN NULL
              WHEN TempA   IS NOT NULL
              AND    Tempb IS NOT NULL
              THEN (TempA + Tempb)/2
              ELSE COALESCE(TempA,Tempb)
       END
FROM
FROM   #temp innner
       JOIN Table2
       ON     Table2.keycol=#temp.key

你应该使用case

UPDATE TableB
SET AvgCol=
case when TempA is null and TempB is null then Null else 
case when TempA is not null and TempB is not null then (TempA+TempB)/2 else
coalesce(TempA, TempB) end  end 
FROM (select KeyCol,
    case when ColA is null and ColB is null then Null else 
    case when ColA is not null and ColB is not null then (ColA+ColB)/2 else
    coalesce(ColA, ColB) end  end as TempA,
    case when ColC is null and ColD is null then Null else 
    case when ColC is not null and ColD is not null then (ColC+ColD)/2 else
    coalesce(ColC, ColD) end  end as TempB
    FROM TableA) TEMP
INNER JOIN TableB
on TEMP.KeyCol=TableB.KeyCol

穿上这个试试看尺码

update Table2
set AvgCol= case when TempA+TempB is null then coalesce(TempA,TempB) else (TempA+TempB)/2 end
from
    Table2 b
        inner join 
(
    select
        case when ColA+ColB is null then coalesce(ColA,ColB) else (ColA+ColB)/2 end as TempA,
        case when ColC+ColD is null then coalesce(ColC,ColD) else (ColC+ColD)/2 end as TempB,
        a.KeyCol
    from TableA a
        inner join Table2 b
            on a.KeyCol=b.KeyCol
) a
    on B.KeyCol=a.KeyCol

如果将数字添加为空,则结果为空。如果两个值都为空,则coalesce将返回空。

为了好玩,这里已经有足够多的其他解决方案:

declare @TempA float, @TempB float

update b set 
  @TempA = coalesce((a.ColA+a.ColB)/2, a.ColA, a.ColB)
, @TempB = coalesce((a.ColC+a.ColD)/2, a.ColC, a.ColD)
, AvgCol = coalesce((@TempA+@TempB)/2, @TempA, @TempB)
from TableA a
join Table2 b on a.KeyCol = b.KeyCol