sql - 在Sql中查找多列的最大值

Question

如何在多列上找到最大值。
这就是我迄今为止所拥有的。

With Temp AS (
    SELECT P.ID AS 'Product_ID',
           P.ProductCode AS 'Product_Code',
           P.Name AS 'Product_Name',
           P.SellPrice AS 'SellPrice',
           P.SellPrice+(P.SellPrice*TVA/100) AS 'PricePerUnit',
           P.TVA AS 'TVA',
           P.Discount AS 'Discount_Product',
           0 AS 'Discount_Code',
           0 AS 'Discount_Newsletter',
           V.ID AS 'Variant_ID',
           V.Nume AS 'Variant_Name',
           V.Stock-V.Reserved AS 'Quantity_MAX',
           T.Quantity AS 'Quantity',
           I.ImageName AS 'Image',
           0 AS 'Is_Kit'
    FROM TemporaryShoppingCart T
    INNER JOIN ProductVariant V ON V.ID=T.Variant_ID
    INNER JOIN Product P ON P.ID=V.ProductID
    LEFT JOIN ProductImage I  ON I.ProductID=P.ID AND DefaultImage=1
    WHERE T.ID=@ID AND T.Variant_ID!=0

 ) SELECT t.* ,MAX(MAXValue) FROM (SELECT (T.Discount_Product) AS 'MAXValue' 
                               UNION ALL 
                               SELECT (T.Discount_Code) 
                               UNION ALL 
                               SELECT (T.Discount_Newsletter)) as 'maxval' //error 
   FROM Temp T

这段代码给了我错误:'maxval' 附近的语法不正确。

Answer 1

您只是在寻找 GREATEST 吗？

SELECT
  t.*, 
  GREATEST(T.Discount_Product, T.Discount_Code, T.Discount_Newsletter) as 'maxval' 
FROM Temp T;

但是，当值为 NULL 时，GREATEST 返回 NULL，因此您可能也想关心这一点。例如:

SELECT 
  t.*, 
  GREATEST
  (
    coalesce(T.Discount_Product,0), 
    coalesce(T.Discount_Code, 0), 
    coalesce(T.Discount_Newsletter, 0)
  ) as 'maxval' 
FROM Temp T;

编辑:如果 GREATEST 在您的 dbms 中不可用，您可以使用 case 表达式。

SELECT 
  t.*, 
  CASE 
    WHEN coalesce(T.Discount_Product, 0) > coalesce(T.Discount_Code, 0)
     AND coalesce(T.Discount_Product, 0) > coalesce(T.Discount_Newsletter, 0)
     THEN coalesce(T.Discount_Product, 0)
    WHEN coalesce(T.Discount_Code, 0) > coalesce(T.Discount_Product, 0)
     AND coalesce(T.Discount_Code, 0) > coalesce(T.Discount_Newsletter, 0)
     THEN coalesce(T.Discount_Code, 0)
    ELSE coalesce(T.Discount_Newsletter, 0)
  END
FROM Temp T;

编辑:要使您自己的语句在语法上正确，请执行以下操作:

SELECT 
  t.*,
  (
    select MAX(Value) 
    FROM 
    (
      SELECT T.Discount_Product AS Value 
      UNION ALL 
      SELECT T.Discount_Code
      UNION ALL 
      SELECT T.Discount_Newsletter
    ) dummy -- T-SQL requires a name for such sub-queries
  ) as maxval
FROM Temp T;