SQL中ROUND()函數(shù)用于四舍五入，基本語法為ROUND(number, decimal_places, [operation])，其中第三參數(shù)非零時(shí)可控制截?cái)唷?.數(shù)據(jù)類型影響結(jié)果，整數(shù)除法需轉(zhuǎn)為浮點(diǎn)；2.與AVG()等聚合函數(shù)連用時(shí)應(yīng)正確嵌套；3.不同數(shù)據(jù)庫處理不同，如MySQL用TRUNCATE()截?cái)?，SQL Server通過第三參數(shù)控制，PostgreSQL按標(biāo)準(zhǔn)四舍五入，SQLite使用“銀行家舍入”。掌握這些要點(diǎn)可避免多數(shù)問題。

Rounding decimal numbers in SQL is pretty straightforward, but it can trip you up if you're not careful with the data types or the specific function syntax in your database system. Most SQL dialects like MySQL, PostgreSQL, and SQL Server support the ROUND() function, but there are nuances worth knowing.

How does the ROUND() function work?

The basic syntax is:

ROUND(number, decimal_places, [operation])

• number: the column or value you want to round
• decimal_places: how many decimal digits you want to keep
• operation (optional): if you include a third argument and set it to anything other than 0, it will truncate instead of round in some databases like SQL Server

For example:

SELECT ROUND(4.678, 2);

This would return 4.68 in most systems.

Keep in mind that behavior can vary slightly depending on your SQL flavor. PostgreSQL rounds away from zero, while SQLite might behave differently in edge cases.

Common issues when rounding decimals

1. Unexpected results due to data types

If you're working with integer types or implicit conversions, SQL might not round the way you expect. For example:

SELECT ROUND(5 / 3, 2);

Depending on your database, this might return 1 instead of 1.67, because 5 / 3 is treated as integer division. To avoid this, cast at least one number to a decimal or float:

SELECT ROUND(5.0 / 3, 2);

Now you'll get 1.67.

2. Rounding with aggregate functions

When using ROUND() with AVG(), make sure to wrap it correctly:

SELECT ROUND(AVG(score), 2) FROM students;

This avoids floating point precision issues and makes the result more readable.

Truncate vs. round in SQL

Some databases allow you to control whether to round or truncate by using the third parameter in ROUND(). For instance, in SQL Server:

SELECT ROUND(4.678, 2, 1); -- Returns 4.67 (truncate)
SELECT ROUND(4.678, 2);    -- Returns 4.68 (round)

Other systems like MySQL don’t support truncation via ROUND(). You’ll need to use TRUNCATE(number, decimal_places) instead.

So always check your SQL dialect’s documentation to be sure.

How to handle rounding in different SQL versions

• MySQL:
  Use ROUND() or TRUNCATE().
  Example:

  SELECT ROUND(3.14159, 2); -- returns 3.14
  SELECT TRUNCATE(3.14159, 2); -- returns 3.14

• PostgreSQL:
  Standard ROUND() works as expected.

  SELECT ROUND(4.65::numeric, 1); -- returns 4.7

• SQL Server:
  Supports truncate via third argument.

  SELECT ROUND(4.655, 2, 1); -- returns 4.65

• SQLite:
  Rounds to the nearest even number when the digit after is exactly 5 — a behavior called "bankers rounding".

---

基本上就這些。只要注意數(shù)據(jù)類型、SQL方言差異和四舍五入還是截?cái)嗟倪x擇，就能避免大部分問題。

以上是如何在SQL中匯總小數(shù)數(shù)？的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！