如何获取多Amount列合并后的最小值？优化现有SQL方案

我有一张名为Archive的表，包含Price1、Price2、Price3三列价格列，对应Amount1、Amount2、Amount3三列数量列，同时还有ID和Batch列。我的需求是：

• 分别过滤出ID='123'、Batch>2986且Batch<6243，同时对应PriceX=7.4的行，取每类过滤结果中AmountX的最小值
• 最终返回这三个最小值中的最小者，仅一行结果

当前使用的SQL（存在问题） #

Select
MIN(Q.Min_Size)  As Min_Min_Size 
From
    (Select
         Min("Archive"."Amount1") As Min_Size
     From
         "Archive"
     Where
         "Archive"."ID" = '123' And
         "Archive"."Price1" = 7.4 And
         "Archive"."Batch" > 2986 And
         "Archive"."Batch" < 6243) Q
Union 
Select
    Q.Min_Size
From
    (Select
         Min("Archive"."Amount2") As Min_Size
     From
         "Archive"
     Where
         "Archive"."ID" = '123' And
         "Archive"."Price2" = 7.4 And
         "Archive"."Batch" > 2986 And
         "Archive"."Batch" < 6243) Q
Group By
    Q.Min_Size
Union 
Select
    Q.Min_Size
From
    (Select
         Min("Archive"."Amount3") As Min_Size
     From
         "Archive"
     Where
         "Archive"."ID" = '123' And
         "Archive"."Price3" = 7.4 And
         "Archive"."Batch" > 2986 And
         "Archive"."Batch" < 6243) Q
Group By
    Q.Min_Size

该语句会返回三行结果，分别是Amount1、Amount2、Amount3列各自的最小值，但我需要仅返回一行，显示这三个值中的最小值。

自行尝试的可行语句（逻辑需注意） #

Select
    Min(Least("Archive"."Amount1", "Archive"."Amount2", "Archive"."Amount3")) As MinAmount
From
    "Archive"
Where
    ("Archive"."ID" = '123' And
        "Archive"."Price1" = 7.4 And
        "Archive"."Batch" > 2986 And
        "Archive"."Batch" < 6243) Or
    ("Archive"."ID" = '123' And
        "Archive"."Batch" > 2986 And
        "Archive"."Batch" < 6243 And
        "Archive"."Price2" = 7.4) Or
    ("Archive"."ID" = '123' And
        "Archive"."Batch" > 2986 And
        "Archive"."Batch" < 6243 And
        "Archive"."Price3" = 7.4)

注：这个语句的逻辑和原需求不一致——它会把满足任意一个PriceX=7.4的行全部取出，然后对每行的三个Amount取最小值，再整体取所有行的最小值。这和原需求中“先分别取每个Price过滤组的Amount最小值，再取这三个值的最小”不是同一个逻辑，可能得到不符合预期的结果。

更优且符合需求的方案 #

方案1：子查询聚合后再取最小 #

把三个列的最小值作为子查询的结果，再外层取整体最小值，逻辑清晰且符合需求：

SELECT MIN(min_val) AS final_min_amount
FROM (
    SELECT MIN("Archive"."Amount1") AS min_val
    FROM "Archive"
    WHERE "Archive"."ID" = '123' 
      AND "Archive"."Price1" = 7.4 
      AND "Archive"."Batch" > 2986 
      AND "Archive"."Batch" < 6243
    
    UNION ALL
    
    SELECT MIN("Archive"."Amount2") AS min_val
    FROM "Archive"
    WHERE "Archive"."ID" = '123' 
      AND "Archive"."Price2" = 7.4 
      AND "Archive"."Batch" > 2986 
      AND "Archive"."Batch" < 6243
    
    UNION ALL
    
    SELECT MIN("Archive"."Amount3") AS min_val
    FROM "Archive"
    WHERE "Archive"."ID" = '123' 
      AND "Archive"."Price3" = 7.4 
      AND "Archive"."Batch" > 2986 
      AND "Archive"."Batch" < 6243
) AS subquery;

这里用UNION ALL代替UNION，避免不必要的去重操作，提升效率。

方案2：使用UNPIVOT（适用于支持该语法的数据库，如SQL Server、Oracle） #

如果数据库支持UNPIVOT，可以将多列转为行，再过滤聚合：

SELECT MIN(amount) AS final_min_amount
FROM "Archive"
UNPIVOT (
    amount FOR price_col IN ("Amount1", "Amount2", "Amount3")
) AS unpivoted
WHERE "ID" = '123' 
  AND "Batch" > 2986 
  AND "Batch" < 6243
  -- 匹配对应的Price列等于7.4
  AND (
      (price_col = 'Amount1' AND "Price1" = 7.4)
      OR (price_col = 'Amount2' AND "Price2" = 7.4)
      OR (price_col = 'Amount3' AND "Price3" = 7.4)
  );

这种方式更灵活，如果后续增加Price/Amount列，只需修改UNPIVOT和WHERE条件即可。

内容的提问来源于stack exchange，提问作者pio pio