查找不包括零的3个值中的最小值

Question

我正在寻找三列中最小的一列(Market, LiFo, Wgtd_Avg)。我可以这样做，但不包括等于零的三个值中的任何一个。

我只想找到那些> zero中最小的一个。

SELECT Market, LiFo, Wgtd_Avg,
(
    CASE
        WHEN Market < LiFo AND Market < Wgtd_Avg AND Market > 0 THEN Market
        WHEN LiFo  < Wgtd_Avg AND LiFo > 0 THEN LiFo
        WHEN Wgtd_Avg > 0 THEN Wgtd_Avg
        ELSE 0
    END
) AS LeastOfThree
FROM VF_CasINV_Cost

Answer 1

原始答案：

SELECT min(Value) FROM
(
SELECT Market as Value FROM VF_CasINV_Cost WHERE Market > 0
UNION
SELECT Wgtd_Avg as Value FROM VF_CasINV_Cost WHERE Wgtd_Avg > 0
UNION
SELECT LiFo as Value FROM VF_CasINV_Cost WHERE LiFo > 0
) union_table

评论后编辑：

SELECT Market, Wgtd_Avg, LiFo, min(least) AS leastOfThree FROM
(
SELECT min(market) as market, min(Market) as least FROM VF_CasINV_Cost WHERE Market > 0
UNION
SELECT min(Wgtd_avg) as Wgtd_Avg, min(Wgtd_Avg) as least FROM VF_CasINV_Cost WHERE Wgtd_Avg > 0
UNION
SELECT min(lifo) as LiFo, min(lifo) as least FROM VF_CasINV_Cost WHERE LiFo > 0
) as union_table

我离开了一台装有sql服务器的计算机，所以现在不能测试它，但它应该已经接近工作了……我一回来就对它进行测试和编辑。

Answer 2

SELECT
  MIN(
    CASE x.f
      WHEN 1 THEN t.Market
      WHEN 2 THEN t.Wgtd_Avg
      WHEN 3 THEN t.LiFo
    END
  ) AS MinValue
FROM VF_Cas_INV_Cost t
  INNER JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) x (f)
    ON CASE x.f
      WHEN 1 THEN t.Market
      WHEN 2 THEN t.Wgtd_Avg
      WHEN 3 THEN t.LiFo
    END > 0