如何用SQL仅基于7位小数判断差值，更新TABLE1的STATUS列

Got it, let's sort out this SQL problem for you. First, let's break down what's wrong with your original query, then build the correct logic step by step.

Issues with Your Original Query #

• The CASE logic is reversed: you're returning "No change" when the difference isn't zero, which is the opposite of what you need.
• Comparing the raw difference (R1_NRI - R2_NRI <> 0) will pick up even tiny differences in the 8th decimal place, which you explicitly want to ignore.

Correct Approach #

We need to ignore any decimal digits beyond the 7th without rounding. The trick here is to scale the values up by 10^7 (10,000,000) and then truncate the remaining decimal part—this way we only compare the first 7 digits after the decimal point.

Here's the corrected SQL:

SELECT 
    Name,
    R1_NRI,
    R2_NRI,
    -- Calculate truncated first 7 decimals for both values and compare
    CASE 
        WHEN FLOOR(R1_NRI * 10000000) = FLOOR(R2_NRI * 10000000)
        THEN 'No change'  -- First 7 decimals match, even if 8th differs
        ELSE 'Changed'    -- First 7 decimals don't match
    END AS STATUS
FROM TABLE1;

Why This Works #

• FLOOR(R1_NRI * 10000000) takes your number, shifts the decimal 7 places right, then drops any remaining decimal digits (no rounding). For example:
  • If R1_NRI = 0.12345678 and R2_NRI = 0.12345671, multiplying by 10^7 gives 1234567.8 and 1234567.1—FLOOR turns both into 1234567, so they're considered equal, resulting in "No change" (exactly what you want for Well3/Well5).
  • If the first 7 decimals differ (e.g., 0.1234567 vs 0.1234568), the truncated values will be different, so we return "Changed".

Database-Specific Adjustments #

If you're using a database where FLOOR isn't the best fit, you can use alternatives:

• SQL Server/Oracle: Use TRUNC(R1_NRI * 10000000) instead of FLOOR—it does the same truncation without rounding.
• Avoiding Overflow: If your R1_NRI/R2_NRI values are very large, multiplying by 10^7 might exceed integer limits. Use a decimal cast to handle this:

  CASE 
      WHEN CAST(R1_NRI * 10000000 AS DECIMAL(20, 0)) = CAST(R2_NRI * 10000000 AS DECIMAL(20, 0))
      THEN 'No change'
      ELSE 'Changed'
  END AS STATUS
  

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