如何在 mysql 查询中舍入计算字段

Question

select *, sum(price+shipping+paypalfee+storefee) as totalcost, customerpaid as totalrevenue, 
(customerpaid - sum(price+shipping+paypalfee+storefee)) as profit,
(((customerpaid - sum(price+shipping+paypalfee+storefee)) / customerpaid) * 100.00) as profitpercent
from tblsales 
group by orderno having " . $having . " 
order by $sort $order limit $offset,$rows"

查询工作正常，我怎么能四舍五入利润百分比，一个计算字段。

Answer 1

使用此处提到的 ROUND 函数 所以你的查询将是：

SELECT orderno, sum(price+shipping+paypalfee+storefee) as totalcost, customerpaid AS totalrevenue, (customerpaid - sum(price+shipping+paypalfee+storefee)) AS profit, ROUND((((customerpaid - sum(price+shipping+paypalfee+storefee)) / customerpaid) * 100.00)) AS profitpercent
FROM tblsales 
GROUP BY orderno HAVING " . $having . " 
ORDER BY $sort $order LIMIT $offset,$rows"

Answer 2

一般

ROUND( expression, 2 )

所以试试这个

select *, sum(price+shipping+paypalfee+storefee) as totalcost, customerpaid as totalrevenue, 
(customerpaid - sum(price+shipping+paypalfee+storefee)) as profit,
ROUND((((customerpaid - sum(price+shipping+paypalfee+storefee)) / customerpaid) * 100.00),2) as profitpercent
from tblsales 
group by orderno having " . $having . " 
order by $sort $order limit $offset,$rows"