如何在MySQL中实现4600取整为4500、4450取整为4000？附尝试代码示例

First, let's address your existing query: the syntax you tried (ROUND((price,-3),TRUNCATE(price,-2.5))) is invalid for two key reasons:

• The ROUND() function only takes two arguments: the number to round and the decimal precision (an integer). You’ve incorrectly nested parameters here.
• TRUNCATE() requires the second parameter to be an integer (you used -2.5, which isn’t allowed). This query will throw a syntax error if you run it.

Now, looking at your examples (4600→4500, 4450→4000, 13650→13500, 20475→20000), your desired rounding rule is clear: round down to the nearest 500 multiple. Let’s break down two simple, valid ways to implement this in MySQL:

Method 1: Simplified Mathematical Expression #

This is the most concise approach using basic arithmetic and the FLOOR() function:

SELECT 
    price,
    FLOOR(price / 500) * 500 AS rounded_price
FROM your_table;

How it works: #

• Divide the price by 500 to convert it into units of 500.
• FLOOR() drops the decimal part to get the largest integer less than or equal to the result.
• Multiply back by 500 to get the rounded value.

Testing with your examples:

• 4600 / 500 = 9.2 → FLOOR(9.2) = 9 → 9*500 = 4500 ✔️
• 4450 / 500 = 8.9 → FLOOR(8.9) = 8 → 8*500 = 4000 ✔️
• 13650 / 500 = 27.3 → FLOOR(27.3) =27 →27*500=13500 ✔️
• 20475 /500=40.95 →FLOOR(40.95)=40 →40*500=20000 ✔️

Method 2: CASE Statement (For Explicit Logic) #

If you want to make the rounding logic more explicit (useful for documentation or future modifications), use a CASE statement with modulo:

SELECT 
    price,
    CASE
        WHEN MOD(price, 1000) >= 500 THEN FLOOR(price / 1000) * 1000 + 500
        ELSE FLOOR(price / 1000) * 1000
    END AS rounded_price
FROM your_table;

How it works: #

• MOD(price,1000) gets the last three digits of the price (e.g., 4600 → 600, 4450 →450).
• If those last three digits are 500 or more, we add 500 to the nearest lower 1000 multiple.
• If they’re less than 500, we just use the nearest lower 1000 multiple.

Both methods will give you exactly the results you’re looking for. The first method is preferred for its brevity and performance.

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