使用 CEILING 時 CASE 表達式返回錯誤值

我遇到了一個CASE表達式沒有返回我期望的問題。

作為測試，我添加了一個十進制變數並對CASE它執行相同的表達式，它工作正常，返回我期望的結果（將值向上舍入 when 。但是當我對另一個十進制值IsGun=1執行相同的表達式時，它總是返回函式CASE的值，CEILING()並且永遠不會返回原始值。

這是SQL程式碼：

DECLARE @Num decimal(8,2);
   set @Num = 12.54;
   WITH PQ AS
   ( 
       SELECT 
           UPC, 
           Price1, 
           DBID,
           AVG(Price1) OVER (PARTITION BY UPC) AS Price1Avg
       FROM
           vProducts_PriceQty_Union
   )
   SELECT 
       PQ.UPC,
       PQ.Price1,
       PQ.Price1Avg,
       (CASE WHEN p.IsGun = 1 THEN CEILING(@Num) ELSE @Num END) AS UsingVar,
       CAST(
           (CASE WHEN P.IsGun = 1 THEN CEILING(PQ.Price1Avg) ELSE PQ.Price1 END)
            AS NUMERIC(8,2))
       AS PriceAdj,
       PQ.DBID,
       P.IsGun
   FROM
       PQ
    INNER JOIN
       products P ON PQ.UPC = P.UPC

以下是結果的片段：

UPC             Price1      Price1Avg   UsingVar    PriceAdj    DBID  IsGun
942000899195    14.9900     14.990000   12.54       15.00       1       0
980420671300    29.9900     29.990000   12.54       30.00       1       0
980420671310    29.9900     29.990000   12.54       30.00       1       0
980426713020    29.9900     29.990000   12.54       30.00       1       0
980426713120    29.9900     29.990000   12.54       30.00       1       0
000998622130    319.0000    319.000000  13.00       319.00      1       1
000998624730    314.0000    314.000000  13.00       314.00      1       1
000998624970    419.0000    419.000000  13.00       419.00      1       1
008244284754    1015.0000   1015.000000 13.00       1015.00     2       1
010633012288    267.0000    267.000000  13.00       267.00      6       1

這是來自vProducts_PriceQty_Union的數據：

UPC             Price1  Price2  Quantity    DBID
942000899195    14.9900 0.0000  2.00        1
980420671300    29.9900 0.0000  3.00        1
980420671310    29.9900 0.0000  1.00        1
980426713020    29.9900 0.0000  2.00        1
980426713120    29.9900 0.0000  1.00        1

從前五個中可以看出，其中IsGun = 0，第一個CASE使用固定變數的表達式返回UsingVar值，正如我們所期望的那樣，12.54。對於最後五個，它還返回我們期望的值 13。

但是在第二個CASE表達式中（完全相同的邏輯），PriceAdj對它們中的每一個都使用該CEILING函式，而不管IsGun = 1 是否。

為什麼查詢沒有返回預期結果？

在一些用於聯合視圖的表中，Price1 和 Price2 的數據類型是smallmoney和decimal ( 8,2 )。從那以後，我將它們全部更改為decimal(8,2)，但這並不影響結果。

要重現問題：

SELECT *, (CASE
   WHEN IsGun=1 THEN CEILING(Price1Avg)
   ELSE Price1 END)
FROM (
   SELECT UPC, IsGun, Price1,
          AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
   FROM (
       VALUES ('A', 0, 14.99),
              ('B', 0, 29.99),
              ('C', 1, 319.00),
              ('D', 1, 314.00)
       ) AS x(UPC, IsGun, Price1)
   ) AS sub;

這裡發生的是CEILING(PQ.Price1Avg)產生一個numeric(38, 0).

根據文件，的輸出類型與CEILING()輸入具有相同的基本數據類型，儘管比例（小數位數）可能會改變，這就是這裡發生的情況。

• 在我的測試中，該AVG()函式返回numeric(38, 6).
• CEILING()但是，該列上的函式輸出numeric(38, 0)：

核實：

SELECT CEILING(CAST(123.45 AS numeric(38, 6)))

作為一種解決方法，您可以顯式轉換CEILING()函式的輸出，這應該會給您正確的結果：

SELECT *, (CASE
   WHEN IsGun=1 THEN CAST(CEILING(Price1Avg) AS numeric(8, 2)) -- Explicit CAST.
   ELSE Price1 END)
FROM (
   SELECT UPC, IsGun, Price1,
          AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg
   FROM (
       VALUES ('A', 0, 14.99),
              ('B', 0, 29.99),
              ('C', 1, 319.00),
              ('D', 1, 314.00)
       ) AS x(UPC, IsGun, Price1)
   ) AS sub;

引用自：https://dba.stackexchange.com/questions/136379