mysql - 在 MySQL 中使用 ROLLUP 时将小数列四舍五入
问题描述
尝试在 MySQL 中使用 ROLLUP 时遇到问题。
我有一个这样创建的表。
create table order_items (
id int,
project_name varchar(50),
order_date date,
name varchar(20),
unit_cost decimal(15,3),
quantity decimal(15,3)
);
并像这样插入数据
insert into order_items (
id, project_name, order_date, name, unit_cost, quantity
) values (
1, "project 1", "2021-02-03", "order 1", 229.432, 14.599
);
insert into order_items (
id, project_name, order_date, name, unit_cost, quantity
) values (
2, "project 1", "2021-02-04", "order 2", 229.634, 15
);
insert into order_items (
id, project_name, order_date, name, unit_cost, quantity
) values (
3, "project 2", "2021-02-04", "order 3", 229.888, 15.543
);
insert into order_items (
id, project_name, order_date, name, unit_cost, quantity
) values (
4, "project 1", "2021-02-05", "order 4", 230.543, 1.5
);
insert into order_items (
id, project_name, order_date, name, unit_cost, quantity
) values (
5, "project 1", "2021-02-05", "order 5", 229.443, 1.5
);
insert into order_items (
id, project_name, order_date, name, unit_cost, quantity
) values (
6, "project 1", "2021-02-03", "order 1", 229.456, 18.234
);
我想按项目汇总每个日期的数量 = unit_cost * 数量,因此我将此查询与 一起使用ROLLUP
,但 unit_cost 和数量列在结果表中四舍五入。(例如,第一行的 unit_cost 应该是 229.432 但它返回 229)
mysql> select order_date, project_name, name, unit_cost, quantity, sum(unit_cost * quantity) as amount from order_items group by order_date, project_name, name, unit_cost, quantity with rollup;
+------------+--------------+---------+-----------+----------+--------------+
| order_date | project_name | name | unit_cost | quantity | amount |
+------------+--------------+---------+-----------+----------+--------------+
| 2021-02-03 | project 1 | order 1 | 229 | 15 | 3349.477768 |
| 2021-02-03 | project 1 | order 1 | 229 | NULL | 3349.477768 |
| 2021-02-03 | project 1 | order 1 | 229 | 18 | 4183.900704 |
| 2021-02-03 | project 1 | order 1 | 229 | NULL | 4183.900704 |
| 2021-02-03 | project 1 | order 1 | NULL | NULL | 7533.378472 |
| 2021-02-03 | project 1 | NULL | NULL | NULL | 7533.378472 |
| 2021-02-03 | NULL | NULL | NULL | NULL | 7533.378472 |
...
+------------+--------------+---------+-----------+----------+--------------+
25 rows in set (0.00 sec)
如果我删除ROLLUP
, unit_cost 和 quantity 会返回到准确的值。
mysql> select order_date, project_name, name, unit_cost, quantity, sum(unit_cost * quantity) as amount from order_items group by order_date, project_name, name, unit_cost, quantity;
+------------+--------------+---------+-----------+----------+-------------+
| order_date | project_name | name | unit_cost | quantity | amount |
+------------+--------------+---------+-----------+----------+-------------+
| 2021-02-03 | project 1 | order 1 | 229.432 | 14.599 | 3349.477768 |
| 2021-02-04 | project 1 | order 2 | 229.634 | 15.000 | 3444.510000 |
| 2021-02-04 | project 2 | order 3 | 229.888 | 15.543 | 3573.149184 |
| 2021-02-05 | project 1 | order 4 | 230.543 | 1.500 | 345.814500 |
| 2021-02-05 | project 1 | order 5 | 229.443 | 1.500 | 344.164500 |
| 2021-02-03 | project 1 | order 1 | 229.456 | 18.234 | 4183.900704 |
+------------+--------------+---------+-----------+----------+-------------+
6 rows in set (0.00 sec)
有什么方法可以使用ROLLUP
但保持对十进制类型的列进行分组的准确值?
MySQL 版本是 8.0.21。
解决方案
尝试了8.0.20、8.0.22、8.0.23版本后,发现这个问题是8.0.21版本引入的bug,在8.0.23已经修复。
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html
添加 WITH ROLLUP 时,某些正确执行的 group by 查询未返回预期结果。这是因为十进制信息并不总是正确地通过汇总组项目传输,导致返回十进制值的函数(例如 TRUNCATE())接收错误类型的数据。(错误 #101684、错误 #32179240)
推荐阅读
- ios - 在 NSNotificationCenter 的通知的 userInfo 中传递空的 NSIndexPath
- sql - 使用选定值计算新值
- javascript - 获取未知选项:运行 yarn build:prod Angular 6 时的“-p”
- python - Python根据特定键从嵌套列表中删除重复项和原始项
- php - 在 PHP 中动态获取键名
- postgresql - 如何重命名 jsonb 列表中的键?
- python - 使用 Python 中的日期列表循环超过 24 小时
- xaml - 如何在 UWP 中全页显示网格?
- jquery - 奇怪的句法行为
- unity3d - Unity,游戏对象的相对尺寸