[解決済み] なぜSQLでは199.96 - 0 = 200になるのですか？

質問

いくつかのクライアントが奇妙な請求書を受け取っています。核心的な問題を分離することができました。

SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 200 what the?
SELECT 199.96 - (0.0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
SELECT 199.96 - (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96)) -- 199.96

SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 199.96
SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4))))                         -- 199.96
SELECT 199.96 - (CAST(0.0 AS DECIMAL(19, 4)) * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96))                         -- 199.96

-- It gets weirder...
SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))) -- 0
SELECT (0 * FLOOR(1.0 * CAST(199.96 AS DECIMAL(19, 4))))                         -- 0
SELECT (0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * 199.96))                         -- 0

-- so... ... 199.06 - 0 equals 200... ... right???
SELECT 199.96 - 0 -- 199.96 ...NO....

誰か、ここで一体何が起こっているのか、手がかりがないでしょうか？つまり、確かに10進データ型と関係があるのですが、どうも腑に落ちないのです...。

数値リテラルがどのようなデータ型であるかについて、多くの混乱がありましたので、実際の行を表示することにしました。

PS.SharePrice - (CAST((@InstallmentCount - 1) AS DECIMAL(19, 4)) * CAST(FLOOR(@InstallmentPercent * PS.SharePrice) AS DECIMAL(19, 4))))

PS.SharePrice DECIMAL(19, 4)

@InstallmentCount INT

@InstallmentPercent DECIMAL(19, 4)

とは異なる型のオペランドを持つ各オペレーションの結果が、それぞれ DECIMAL(19, 4) とは異なる型のオペランドを持つ各オペレーションの結果は、外側のコンテキストに適用する前に明示的にキャストされるようにしました。

それでも、結果は 200.00 .

私は今、皆さんが自分のコンピュータで実行できるように、煮詰めたサンプルを作成しました。

DECLARE @InstallmentIndex INT = 1
DECLARE @InstallmentCount INT = 1
DECLARE @InstallmentPercent DECIMAL(19, 4) = 1.0
DECLARE @PS TABLE (SharePrice DECIMAL(19, 4))
INSERT INTO @PS (SharePrice) VALUES (599.96)

-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * PS.SharePrice),
  1999.96)
FROM @PS PS

-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * CAST(599.96 AS DECIMAL(19, 4))),
  1999.96)
FROM @PS PS

-- 1996.96
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * 599.96),
  1999.96)
FROM @PS PS

-- Funny enough - with this sample explicitly converting EVERYTHING to DECIMAL(19, 4) - it still doesn't work...
-- 2000
SELECT
  IIF(@InstallmentIndex < @InstallmentCount,
  FLOOR(@InstallmentPercent * CAST(199.96 AS DECIMAL(19, 4))),
  CAST(1999.96 AS DECIMAL(19, 4)))
FROM @PS PS

今、私は何かを持っている...

-- 2000
SELECT
  IIF(1 = 2,
  FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))),
  CAST(1999.96 AS DECIMAL(19, 4)))

-- 1999.9600
SELECT
  IIF(1 = 2,
  CAST(FLOOR(CAST(1.0 AS decimal(19, 4)) * CAST(199.96 AS DECIMAL(19, 4))) AS INT),
  CAST(1999.96 AS DECIMAL(19, 4)))

なんだよー、floorってどうせ整数を返すんだろ。どうなってるんだ？

私は今、本当に本質的なところまで煮詰めることができたと思います :-D

-- 1.96
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (36, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

-- 2.0
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (37, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

-- 2
SELECT IIF(1 = 2,
  CAST(1.0 AS DECIMAL (38, 0)),
  CAST(1.96 AS DECIMAL(19, 4))
)

どのように解決するのですか？

まずはこれを少し解いて、何が起こっているのか分かるようにする必要があります。

SELECT 199.96 - 
    (
        0.0 * 
        FLOOR(
            CAST(1.0 AS DECIMAL(19, 4)) * 
            CAST(199.96 AS DECIMAL(19, 4))
        )
    ) 

ここで、SQL Serverが引き算の操作のそれぞれの側でどのような型を使用しているかを正確に見てみましょう。

SELECT  SQL_VARIANT_PROPERTY (199.96     ,'BaseType'),
    SQL_VARIANT_PROPERTY (199.96     ,'Precision'),
    SQL_VARIANT_PROPERTY (199.96     ,'Scale')

SELECT  SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'BaseType'),
    SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'Precision'),
    SQL_VARIANT_PROPERTY (0.0 * FLOOR(CAST(1.0 AS DECIMAL(19, 4)) * CAST(199.96 AS DECIMAL(19, 4)))  ,'Scale')

結果

数字 5 2
数値38 1

そこで 199.96 は numeric(5,2) であり、より長い Floor(Cast(etc)) は numeric(38,1) .

は のルールは、結果の精度とスケール は、引き算の演算（つまり e1 - e2 ) のルールは次のようになります。

精度です。 max(s1, s2) + max(p1-s1, p2-s2) + 1 です。

スケールです。 max(s1, s2)

このように評価されます。

精度です。 max(1,2) + max(38-1, 5-2) + 1 => 2 + 37 + 1 => 40

スケール。 max(1,2) => 2

また、ルールのリンクから、どこに numeric(38,1) がそもそもどこから来たのか（ヒント：2つの精度19の値を掛け合わせた）を知るために、ルールのリンクを使うこともできます。

しかし

• 結果精度とスケールの絶対最大値は38です。結果の精度が38より大きい場合、38に縮小され、対応するスケールも縮小されます。 対応するスケールは，結果の積分部分が切り捨てられるのを防ごうとするため，38に縮小される。 を切り捨てないようにする。乗算や除算のような場合，スケールファクターが減少しないこともある。 乗算や除算の場合，10進数の精度を保つため，倍率を下げないこともあります。 オーバーフローエラーを発生させることができます。

おっとっと。精度は40です。精度を下げると、常に最下位桁をカットしなければならないので、スケールも下げることになります。 最終的に得られる式の型は numeric(38,0) で、これは 199.96 に丸められます。 200 .

を移動して統合することで解決できるかもしれません。 CAST() の操作を大きな式の内部から 1 CAST() のように、式全体の結果を囲みます。だから、これ。

SELECT 199.96 - 
    (
        0.0 * 
        FLOOR(
            CAST(1.0 AS DECIMAL(19, 4)) * 
            CAST(199.96 AS DECIMAL(19, 4))
        )
    ) 

になる。

SELECT CAST( 199.96 - ( 0.0 * FLOOR(1.0 * 199.96) ) AS decimial(19,4))

外側のキャストも外すかもしれませんね。

私たちはここで、実際に持っている精度や規模に合わせて型を選択すべきことを学びました 今 に合わせて型を選択する必要があることを学びました。SQL Server は算術演算中にオーバーフローを回避するためにこれらの型を変更するからです。

より詳細な情報。

• を用いた精度、スケール、長さ Sql_Variant_Property()
• 演算子の優先順位
• データ型の優先順位