php 如何在 SQL SELECT 查询中做简单的数学运算？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11496544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do simple math in an SQL SELECT query?
提问by eric
I need to select all products whose fields conform to a simple mathematical expression (in my case, division).
我需要选择其字段符合简单数学表达式（在我的例子中，除法）的所有产品。
Example Query:
示例查询：
SELECT * FROM cars WHERE (retail_price/invoice_price)>1.05
Needs to be flexible:
I need flexibility here because I wish to be able to define the expression as a string somewhere else and just pop it in the SQL query after the WHERE clause.
需要灵活：
我在这里需要灵活性，因为我希望能够将表达式定义为其他地方的字符串，并在 WHERE 子句之后的 SQL 查询中弹出它。
$expression = "(retail_price/invoice_price)>1.05";
$query = "SELECT * FROM cars WHERE ".$expression." LIMIT 1";
Notes:
- Data type of retail_price is VARCHAR
- Data type of invoice_price is VARCHAR
- Not sure if VARCHAR is suitable for monetary values though..
注意：
-retail_price 的数据类型是 VARCHAR
-invoice_price 的数据类型是 VARCHAR-
不确定 VARCHAR 是否适用于货币价值。
Any suggestions? Because the above did not work for me. (Returned zero results)
有什么建议？因为以上对我不起作用。（返回零结果）
回答by Marco
It could be a rounding problem (as @Justin said).
Try this:
这可能是一个舍入问题（正如@Justin 所说）。
尝试这个：
SELECT * FROM cars WHERE (1.0 * retail_price / invoice_price) > 1.05
The idea is to force engine to manage floats (multiplying for float number 1.0).
这个想法是强制引擎管理浮点数（乘以浮点数 1.0）。
UPDATE:
I see you've just edited data type to VARCHAR.
Anyway the result (for me) is the same: look at http://sqlfiddle.com/#!2/298d3/2
更新：
我看到您刚刚将数据类型编辑为 VARCHAR。
无论如何，结果（对我来说）是一样的：看看http://sqlfiddle.com/#!2/298d3/2
