如何在MySQL中从同一张表中减去两个计算字段？

Question

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

如果我省略(totalcost - totalrevenue) as profit，查询就能正常运行。如何在同一查询中使用totalcost和totalrevenue计算PROFIT呢？

- Bubba Yakoza

Answer 1

你的问题的答案是，你需要重复表达式：

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

您不允许在定义别名的同一select中使用该列别名。

另外，您的查询看起来很奇怪。任何具有select *和group by的查询都是可疑的。您有许多列（可能）其值将来自每个组的不确定行。通常应明确列出列，但对于group by尤其如此。

- Gordon Linoff

---

谢谢你提供的解决方案。我可以问一下为什么要明确使用列名吗？我的意思是背后的逻辑是什么。 - Bubba Yakoza

@BubbaYakoza . . . 你明白 group by 的作用吗？在 select 中有列不在 group by 列表中通常是没有意义的。也许扩展文档可以帮到你：http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html。 - Gordon Linoff

Answer 2

您可以这样做： SELECT *，(totalcost-totalrevenue) AS profit FROM( SELECT *， SUM(price+shipping+paypalfee+storefee) AS totalcost， customerpaid AS totalrevenue,

FROM tblsales GROUP BY orderno HAVING " . $having . " ORDER BY $sort $order ) LIMIT $offset,$rows

- Ajit Kumar