Rails 3-MySQL返回与Postgresql不同的结果 [英] Rails 3 - mysql returning different result than postgresql
问题描述
我正在尝试在查询中进行一些计算,我认为postgresql处理数学的方式与mysql不同,但我不确定区别在哪里.这是我从控制器发出的查询:
I am trying to do some calculations in my query and i think postgresql is handling the math differently than mysql is but i'm not sure where the difference is. This is my query from my controller:
Invoice.find(params[:id], :joins => :invoice_line_items, :select => "invoices.id, SUM(invoice_line_items.hours * invoice_line_items.rate) as subtotal, SUM(invoice_line_items.hours * invoice_line_items.rate) - (SUM(invoice_line_items.hours * invoice_line_items.rate) * (invoices.discount_percentage / 100)) as total", :group => "invoices.id")
在开发环境中,我使用mysql,在生产环境中,我使用postgresql.这些是我得到的输出.作为参考,Discount_percentage为20.
In my development environment i am using mysql and in my production environment i am using postgresql. These are the outputs that i am getting. For reference, the discount_percentage is 20.
MySQL:
| id | subtotal | total |
----------------------------------------
| 21 | 570.0000 | 456.00000000 |
PostgreSQL:
| id | subtotal | total |
----------------------------------------
| 9 | 570.0000 | 570.00000000 |
似乎与百分比和总数有关.有人有主意吗?顺便说一句,MySQL的结果就是我想要的.
It looks like it's something to do with the percentage and total. Anyone have any ideas? By the way, the MySQL result is what i am wanting.
推荐答案
Postgres正在执行整数除法.
Postgres is doing integer division.
Select MySQL Postgres
----------------------- ----- --------
select 999 / 100 as a 9.99 9
select 999 / 100.0 as a 9.99 9.99
因此,将 x/100
更改为 x/100.0
,以使它们的行为相同.
So, change the x / 100
to x / 100.0
to get them both to behave the same.
这篇关于Rails 3-MySQL返回与Postgresql不同的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!