有一年我接了一个学生统计报表需求是从 student 表里求各班平均年龄保留两位小数。我第一版 SQL 写得很自信SELECT SUM(age) / COUNT(age) FROM student GROUP BY class。结果在 SQL Server 上一跑屏幕上一排整数20、19、21。业务方问我小数去哪儿了我还以为是数据问题排查了半天才发现SQL Server 的整型除法直接把小数吃了。这个坑看起来很小但在实际项目里引发的连锁反应一点都不小后面所有按比例、平均值、占比算出来的数字都可能从根上就错了。所以今天专门来聊 SQL 里除法那些事儿怎么保留整数怎么保留几位小数ROUND、CAST、FLOOR、CEILING 这些函数在除法场景下到底该怎么用除数为 0 怎么兜底。内容以 SQL Server 为主同时补上 MySQL 和 Oracle 的常见写法都是我实际踩过坑之后沉淀下来的经验。1. 整型相除的“地板效应”为什么年龄相除后小数不见了1.1 先复现一个最典型的整型除法场景假设一张学生表里只有两行数据年龄分别是 19 和 20SELECT SUM(age) / COUNT(age) FROM student;在 SQL Server 里执行结果不是 19.50而是 19。为什么因为age是intSUM(age)的返回类型是intCOUNT(age)的返回类型也是int。两个整数相除SQL Server 会按整数除法处理直接舍去小数部分。这不是四舍五入也不是向上向下取整就是把小数点后面的东西全部扔掉结果还是整数。看几个更直观的例子SELECT 7 / 2; -- 3 SELECT 7.0 / 2; -- 3.500000 SELECT 7 / 2.0; -- 3.500000 SELECT 7 * 1.0 / 2; -- 3.500000 SELECT CAST(7 AS DECIMAL(10,2)) / 2; -- 3.500000只要有一个操作数变成带小数的数值类型除法结果就会自动提升成小数类型。7 / 2是 37.0 / 2就是 3.5这就是新手最容易忽略的类型推断问题。1.2 SQL Server 和 MySQL 的默认行为并不一样MySQL 的默认行为和 SQL Server 不太一样SELECT 7 / 2; -- 3.5000 SELECT 7 DIV 2; -- 3MySQL 里7 / 2默认返回 3.5000因为 MySQL 的/运算符对于整数操作数默认产生带小数的结果。而DIV才是整数除法直接返回 3。Oracle 里两个整数相除会直接返回小数比如SELECT 7 / 2 FROM DUAL;得到 3.5。所以网上搜“SQL 除法”的时候你可能会看到完全相反的结论。不是他们写错了而是不同数据库的默认规则不同。这也是为什么在跨数据库做数据迁移或者写通用 SQL 时必须主动做类型转换不能依赖于某个数据库的默认行为。1.3 解决思路把分子或分母显式变成小数回到平均年龄的例子。改法很简单核心思路就是“让除法的任何一侧先变成带小数点的数”SELECT SUM(age) * 1.0 / COUNT(age) FROM student;1.0在 SQL Server 里会被解析为numeric类型整型SUM(age)乘上numeric后除法结果就带小数了。更规范、可读性更好的写法是用CAST显式转换SELECT CAST(SUM(age) AS DECIMAL(10,2)) / COUNT(age) FROM student;这样写有几个好处第一明确告诉读代码的人这里需要除法保留小数第二CAST(SUM(age) AS DECIMAL(10,2))顺手把SUM(age)原本可能溢出的整型范围也扩大了一档第三结果更容易控制精度。注意* 1.0虽然省事但在代码评审时容易被人问“这个 1.0 是干什么的”。我更推荐直接用CAST把意图写清楚后续维护成本低很多。2. 保留 N 位小数ROUND、CAST、CONVERT 到底该用谁2.1 ROUND 是四舍五入但保留不了末位的 0ROUND是最常见的保留小数函数。SQL Server、MySQL、Oracle 都有写法也基本一致ROUND(数值, 位数)。SELECT ROUND(19.6666, 2); -- 19.67但是这里有一个特别容易踩的坑ROUND返回的依旧是数值类型如果结果是整数它不会自动给你补出小数点后的 0。SELECT ROUND(20.00, 2); -- 20.00实际显示可能只是 20在 SQL Server 里SELECT ROUND(20.00, 2)的结果类型还是 numeric来的数据是 20.00但客户端展示、Excel 导出、JSON 序列化的时候很可能会把末尾的 0 丢掉。业务方如果说“保留两位小数”他看到的往往是展示层的结果而不是数据库里的内部表示。所以如果你的目标是“最终展示为 20.00”不能只靠 ROUND。另一个问题ROUND对float类型的结果不一定可靠。比如ROUND(2.675, 2)在某些数据库里可能得到 2.67而不是 2.68。因为 2.675 在二进制浮点里不是一个精确值存储的实际值可能略小于 2.675。要避免这种问题涉及金额、统计结果时尽量用DECIMAL不要用FLOAT/REAL。2.2 CAST 和 CONVERT让结果自动带两位小数如果你要的不是“约等于两位小数”而是“数据类型上就是两位小数”那就该用CAST或CONVERT。SQL Server 写法SELECT CAST(SUM(age) * 1.0 / COUNT(age) AS DECIMAL(10,2)); SELECT CONVERT(DECIMAL(10,2), SUM(age) * 1.0 / COUNT(age));MySQL 写法SELECT CAST(SUM(age) / COUNT(age) AS DECIMAL(10,2));这两者都会把结果“四舍五入到两位小数”同时返回的类型精度就是两位小数。只要客户端支持结果会显示成 19.50 这种格式。但有一件事必须注意类型转换要放在除法完成之后不能放在除法之前。SELECT CONVERT(DECIMAL(10,2), 7 / 2);这个结果是 3.00不是 3.50。因为括号里面7 / 2先按整数除法算出了 3之后再怎么CONVERT也变不出小数位了。你要么先让分子分母变成小数要么把CAST放到除法表达式的整体外面两个都要占住。2.3 FORMAT、STR 和 TO_CHAR从“数字”到“展示文本”如果业务需求是“导出报表时一定要显示两位小数”此时你要的其实是字符串不是数值。SQL Server 里可以用FORMATSELECT FORMAT(SUM(age) * 1.0 / COUNT(age), 0.00);FORMAT是 SQL Server 2012 以后才有的底层走的是 .NET 格式化写起来很直观但性能比较差。几十万行的报表里大量用FORMAT响应时间会明显变长。能用CAST或者STR的时候尽量别为了一时的方便用FORMAT。还有个冷门函数STRSELECT STR(19.5, 10, 2); -- 19.50STR(数值, 总长度, 小数位数)会返回一个右对齐的字符串前面带空格。你往往需要再套一层LTRIM或RTRIM来处理空格SELECT LTRIM(STR(19.5, 10, 2)); -- 19.50MySQL 里的FORMAT会顺带把千分位也带上比如FORMAT(12345.5, 2)得到12,345.50。如果你不想要逗号就还是用CAST(ROUND(...) AS DECIMAL(10,2))。Oracle 里最常用的其实是TO_CHARSELECT TO_CHAR(19.5, FM9990.00) FROM DUAL; -- 19.50FM是去掉多余空格9990.00是模板。这种写法灵活但模板本身有学习成本而且位数模板写小了会直接变成#所以 Oracle 老手通常会在工具里存几套常用模板比如FM999999990.00。2.4 百分比场景怎么保留两位小数报表里最常见的除法不是算平均年龄而是算占比。例如统计每个班级人数占全年级总人数的百分比SELECT class_name, COUNT(*) AS class_cnt, CAST(100.0 * COUNT(*) / NULLIF((SELECT COUNT(*) FROM student), 0) AS DECIMAL(10,2)) AS pct FROM student GROUP BY class_name;这里有两个关键点。第一100.0 * COUNT(*)是故意把分子变成小数的这样后续除法不会丢精度第二比例通常要的是“百分比数值”所以把结果乘以 100而不是在%字符串上再格式化。如果业务方要显示成35.20%你可以在最外层用字符串拼接比如FORMAT(..., 0.00) %但底层的数据最好还是存成DECIMAL(10,2)的数值。3. 保留整数你真的要四舍五入吗3.1 普通四舍五入到整数如果只是“保留整数”很多人第一反应是SELECT ROUND(19.5, 0); -- SQL Server 返回 20.0ROUND的第二个参数写 0表示保留 0 位小数。注意它返回的仍然是 numeric比如 20.0不是 int。如果你要的就是纯整数可以再包一层CASTSELECT CAST(ROUND(19.5, 0) AS INT); -- 20但这里有个陷阱别直接用CAST(19.5 AS INT)来取整。因为有些数据库把小数转整数时会做四舍五入有些是直接截断行为并不统一。而且CAST的舍入规则往往会受数据库中float、decimal转换策略影响不如显式写ROUND加CAST来得可靠。3.2 向上取整与向下取整的业务含义保留整数的需求背后往往隐藏着不同的业务规则向上取整分页算总页数、库存拆箱、材料算用料不能少算一层。向下取整计算可以完整处理的批次数多出来的零头单独处理。四舍五入对精度要求不严格的统计展示。SQL Server 用CEILING向上取整FLOOR向下取整SELECT CEILING(19.2); -- 20 SELECT FLOOR(19.8); -- 19MySQL 里向上取整是CEIL或CEILING向下取整是FLOORSELECT CEIL(19.2); -- 20 SELECT FLOOR(19.8); -- 19Oracle 和 MySQL 一样函数名是CEIL和FLOOR。负数场景下尤其要小心。FLOOR(-1.2)在 SQL Server 里返回 -2因为它是“小于或等于该数的最大整数”CEILING(-1.2)返回 -1因为它是“大于或等于该数的最小整数”。如果业务上对负数有特殊要求比如财务冲红、退款的批次计算建议先把负数的取整规则摸清楚再决定用哪个函数。3.3 注意 MySQL 的 DIV 和 SQL Server 的整数除法不是一回事MySQL 提供了专门做整数除法的DIV运算符SELECT 7 DIV 2; -- 3 SELECT 7.9 DIV 2; -- 3DIV会直接返回整数部分不会做四舍五入。但 SQL Server 没有DIV它靠的是操作数类型隐式决定。如果你把 SQL Server 的7 / 2当成“除法”而把 MySQL 的7 DIV 2也当成“除法”这两者语义完全不同。跨数据库迁移时这类细节经常会把数据算错。3.4 空值处理与除数为 0NULLIF 三步走除数为 0 是除法里最硬核的雷。SQL Server 默认会直接报错Divide by zero error encountered.Oracle 会报ORA-01476: divisor is equal to zeroMySQL 默认返回NULL但在严格模式下可能也会报错。通用兜底写法是用NULLIF把 0 变成NULL再用COALESCE给一个默认值SELECT COALESCE(SUM(amount) * 1.0 / NULLIF(COUNT(*), 0), 0) FROM orders;原理拆开看就三步NULLIF(COUNT(*), 0)如果分母等于 0就返回NULLSUM(amount) * 1.0 / NULL任何数除以NULL的结果是NULLCOALESCE(..., 0)把NULL转成业务上能接受的默认值比如 0。这套写法在 SQL Server、MySQL、Oracle 里都通用。唯一要注意的是COALESCE的默认值类型要跟表达式结果类型兼容不然可能在隐式转换上又栽一个跟头。4. 数据类型的暗坑精度、溢出与隐式转换4.1 DECIMAL 精度和标度怎么选DECIMAL(10,2)里的两个参数经常有人搞混。第一个是总位数第二个是小数位数。DECIMAL(10,2)表示整数部分最多 8 位小数部分 2 位最大能存 99999999.99。如果你只写一个很大的数字比如CAST(123456789 / 2 AS DECIMAL(10,2))可能不会报错但CAST(123456789.55 AS DECIMAL(10,2))就会溢出。因为总位数 10 位小数点前 8 位9 位整数就超了。除法场景里还有一层麻烦两个DECIMAL相除时数据库会自动推算出更高的小数位。例如 SQL Server 计算DECIMAL(10,2) / DECIMAL(10,2)结果的小数位往往是 4 位甚至更多。所以完整写法最好是在最外层再包一层CAST把最终结果钉死到你想要的精度SELECT CAST( CAST(100 AS DECIMAL(10,2)) / CAST(3 AS DECIMAL(10,2)) AS DECIMAL(10,2) ); -- 33.33内层负责计算外层负责收口这样不管中间的推算规则多复杂最终结果一定可控。4.2 聚合函数 SUM 和 COUNT 也会埋雷很多人在除法里用SUM(age)但没有考虑过SUM的返回类型。SQL Server 里SUM(int)返回int当数据量很大时整数求和有可能超过int的范围直接报“Arithmetic overflow error”。比如一张流水表里用 int 存数量一年几千万行个别分组求和很容易超过 21 亿。所以做统计除法前最好先把聚合结果转成更大的类型SELECT CAST(SUM(CAST(qty AS BIGINT)) AS DECIMAL(18,2)) / NULLIF(COUNT(*), 0) FROM detail_table;这行代码看起来啰嗦但能同时解决两个问题一是避免大数求和溢出二是让除法结果保留小数。不要等到生产环境报错才回头补类型这种错往往数据量一大就爆出来。4.3 隐式转换是慢 SQL 和错数的共同来源除法相关的隐式转换最典型的是把DECIMAL和FLOAT混着算。比如一列是DECIMAL(10,2)另一列是FLOATSQL Server 会为了兼容自动把低精度类型转成高精度类型这个过程中浮点数误差会被放大。另一个问题是如果除法式子写在WHERE条件里并且对索引列做了计算索引基本就废了。例如WHERE total / days 100如果total是索引列这个条件很难利用索引。可以改写成WHERE total days * 100当然前提是days不会为负或者 0并且改写前后语义一致。这种改写不仅能避开除法还能帮助优化器走索引是另一个层面的“除法优化”。5. 一份可以直接抄的 SQL 模板跨 SQL Server / MySQL / Oracle5.1 求平均值并保留两位小数学生年龄示例把开头的平均年龄问题完整写出来。SQL Server 版本SELECT class_name, CAST(SUM(CAST(age AS BIGINT)) * 1.0 / NULLIF(COUNT(*), 0) AS DECIMAL(10,2)) AS avg_age FROM student GROUP BY class_name;SUM(CAST(age AS BIGINT))先把年龄提升为BIGINT防止班级人数特别多时溢出* 1.0再提升为带小数类型NULLIF(COUNT(*), 0)做除零保护外层CAST收口到两位小数。MySQL 版本可以简化因为/本身就返回小数SELECT class_name, CAST(SUM(age) / COUNT(*) AS DECIMAL(10,2)) AS avg_age FROM student GROUP BY class_name;如果希望展示层一定出现.00可以改用FORMAT但要注意它会给数字加千分位。平均年龄一般不会上千用起来倒也无妨。Oracle 版本SELECT class_name, TO_CHAR(ROUND(SUM(age) / COUNT(*), 2), FM9990.00) AS avg_age FROM student GROUP BY class_name;ROUND负责四舍五入TO_CHAR负责固定两位显示。没有用CAST因为 Oracle 里用TO_CHAR做展示更自然。5.2 计算占比并显示百分数SQL ServerSELECT category, COUNT(*) AS cnt, CAST(100.0 * COUNT(*) / NULLIF((SELECT COUNT(*) FROM orders), 0) AS DECIMAL(10,2)) AS pct FROM orders GROUP BY category;MySQL 同样可以跑这一段因为100.0 * COUNT(*)已经让分子变成小数CAST收口到两位。Oracle 把CAST(... AS DECIMAL(10,2))换成TO_CHAR(ROUND(..., 2), FM9990.00)即可。注意pct直接存的是“35.20”这样的数值而不是“35.20%”。这样方便后续排序、比较或继续做加减计算。如果报表非要在界面上带百分号那是展示层的事不要污染数据层。5.3 计算分页总页数向上取整分页查询时总页数必须向上取整否则最后一条数据会被漏掉。常见的错误写法是直接COUNT(*) / PageSize比如 101 条数据每页 20 条算出来是 5 页实际需要 6 页。SQL ServerDECLARE PageSize INT 20; SELECT CEILING(COUNT(*) * 1.0 / PageSize) AS total_pages FROM articles;COUNT(*) * 1.0是核心确保除法是小数除法CEILING再向上取整。MySQL 把CEILING换成CEILOracle 也一样用CEIL。如果不想写* 1.0也可以写CAST(COUNT(*) AS DECIMAL(10,2)) / PageSize效果一样。总分页数这种场景不涉及金额精度用* 1.0更简洁但团队规范如果要求显式转换就统一用CAST。5.4 “抄作业”时的参数调整清单最后整理一份速查清单按场景查找即可场景推荐写法提醒除法结果要参与后续计算保留两位小数CAST(分子 * 1.0 / 分母 AS DECIMAL(10,2))先提升类型再做除法除法结果只给人看必须显示.00SQL ServerFORMAT/ MySQLFORMAT/ OracleTO_CHARFORMAT性能一般除数为 0 不让报错COALESCE(a * 1.0 / NULLIF(b, 0), 0)默认值类型要兼容保留整数且四舍五入CAST(ROUND(a * 1.0 / b, 0) AS INT)别直接用CAST(a/b AS INT)向上取整SQL ServerCEILING/ MySQLCEIL/ OracleCEIL注意负数的边界向下取整FLOOR注意负数的边界百分比展示CAST(100.0 * 分子 / 分母 AS DECIMAL(10,2))百分符号放在展示层处理5.5 如果只看一句话我写 SQL 的时候已经养成习惯了分子用CAST转成DECIMAL分母用NULLIF包一层最外层再用CAST或FORMAT收口。这套动作做熟了除法从算出错到展示不对的路基本就都堵死了。SQL 里最贵的时间不是写代码而是上线后才发现某个统计数字从根上就不对。除法这种事情宁可写之前多花两分钟把类型想清楚也别等业务方甩一张错报表过来再回头查。