Mod negative numbers in SQL just like excel

问题:

I'm having trouble replicating the mod function in SQL sever.

In excel, mod (-3, 7) = 4. But in SQL, -3 % 7 = -3

Am I using % wrong, or does SQL do mod differently?

回答1:

This will give a result between 0 and n - 1 for both positive and negative values of x:

((x % n) + n) % n

回答2:

Well, modular arithmetic is done on equivalence classes of integers, so neither Excel nor any RDBMS is "doing % wrong". If you want a representative between 0 and 6, though, you can always do

select (-3 % 7) + 7;