找到三个中的最小值非常有效，但是如何将每行的值保存到表中呢?

Question

DECLARE @LeastOf3 numeric
BEGIN TRAN
SELECT Item#, ID, Market, LiFo, Wgtd_Avg,
(
    CASE
        WHEN Market = Wgtd_Avg and Wgtd_Avg = LiFo THEN Market
        WHEN Lifo = 0 and Wgtd_Avg = 0 and Market <> 0 THEN Market
        WHEN Market < LiFo AND Market < Wgtd_Avg AND Market <> 0 THEN Market
        WHEN Market <= Wgtd_Avg AND LiFo = 0 THEN Market
        WHEN Market <= LiFo  AND Wgtd_Avg = 0 THEN Market
        WHEN Market = 0 and Wgtd_Avg = 0 and LiFo <> 0 THEN LiFo
        WHEN LiFo < Market AND LiFo < Wgtd_Avg AND LiFo <> 0 THEN LiFo
        WHEN LiFo <= Market  AND Wgtd_Avg = 0 THEN LiFo
        WHEN LiFo <= Wgtd_Avg  AND Market = 0 THEN LiFo
        WHEN Market = 0 and LiFo = 0 and Wgtd_Avg <> 0 THEN Wgtd_Avg
        WHEN Wgtd_Avg < Market and Wgtd_Avg < LiFo and Wgtd_Avg <> 0 THEN Wgtd_Avg
        WHEN Wgtd_Avg <= Market AND LiFo = 0 THEN Wgtd_Avg
        WHEN Wgtd_Avg <= LiFo AND Market = 0 THEN Wgtd_Avg
        WHEN Market <= LiFo and LiFo < Wgtd_Avg  and Market <> 0 THEN Market
        WHEN LiFo <= Market and Market < Wgtd_Avg  and LiFo <> 0 THEN LiFo
        WHEN Wgtd_Avg <= LiFo and LiFo < Market and Wgtd_Avg <> 0  THEN Wgtd_Avg
        ELSE 0
    END
) AS LeastOf3
FROM VF_CasINV_Cost
where (CalendarYear = 2010) and (Item# < 99999990)
--SET LeastOfThree = LeastOf3

--UPDATE VF_CasINV_Cost
--SET LeastOfThree = @LeastOf3
--where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990)
--go

Answer 1

使用UPDATE FROM语句，就像这样:

UPDATE VF_CasINV_Cost 
SET LeastOfThree = LeastOf3
FROM
SELECT (... your big select statement...)

Answer 2

您只需要在UPDATE语句中包含您的CASE，而不是您试图使用的局部变量，如:

UPDATE VF_CasINV_Cost
SET LeastOfThree = --<<your big case here
where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990)

所以这是最后的代码:

UPDATE VF_CasINV_Cost
SET LeastOfThree = CASE
                       WHEN Market = Wgtd_Avg and Wgtd_Avg = LiFo THEN Market
                       WHEN Lifo = 0 and Wgtd_Avg = 0 and Market <> 0 THEN Market
                       WHEN Market < LiFo AND Market < Wgtd_Avg AND Market <> 0 THEN Market
                       WHEN Market <= Wgtd_Avg AND LiFo = 0 THEN Market
                       WHEN Market <= LiFo  AND Wgtd_Avg = 0 THEN Market
                       WHEN Market = 0 and Wgtd_Avg = 0 and LiFo <> 0 THEN LiFo
                       WHEN LiFo < Market AND LiFo < Wgtd_Avg AND LiFo <> 0 THEN LiFo
                       WHEN LiFo <= Market  AND Wgtd_Avg = 0 THEN LiFo
                       WHEN LiFo <= Wgtd_Avg  AND Market = 0 THEN LiFo
                       WHEN Market = 0 and LiFo = 0 and Wgtd_Avg <> 0 THEN Wgtd_Avg
                       WHEN Wgtd_Avg < Market and Wgtd_Avg < LiFo and Wgtd_Avg <> 0 THEN Wgtd_Avg
                       WHEN Wgtd_Avg <= Market AND LiFo = 0 THEN Wgtd_Avg
                       WHEN Wgtd_Avg <= LiFo AND Market = 0 THEN Wgtd_Avg
                       WHEN Market <= LiFo and LiFo < Wgtd_Avg  and Market <> 0 THEN Market
                       WHEN LiFo <= Market and Market < Wgtd_Avg  and LiFo <> 0 THEN LiFo
                       WHEN Wgtd_Avg <= LiFo and LiFo < Market and Wgtd_Avg <> 0  THEN Wgtd_Avg
                       ELSE 0
                   END
where (CalendarYear = 2010) AND (Item# = Item#) AND (ID = ID) and (Item# < 99999990)

Answer 3

我试着让你的陈述更简短

UPDATE VF_CasINV_Cost SET LeastOfThree =
(select coalesce(min(low), 0) from 
(select market low where market > 0 union all
select wgtd where wgtd > 0 union all
select lifo where lifo > 0 union all select null) a
)
WHERE CalendarYear = 2010

这证明它是有效的

declare @t table (LeastOfThree int, market int, wgtd int, lifo int)
insert @t values (null, 1,2,0)
UPDATE @t SET LeastOfThree =
(select coalesce(min(low), 0) from 
(select market low where market > 0 union all
select wgtd where wgtd > 0 union all
select lifo where lifo > 0 union all select null) a
)
select * from @t