MySQL select语句中的mysql计算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16318730/
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
Mysql calculation in select statement
提问by Law
I have been doing my office work in Excel.and my records have become too much and want to use mysql.i have a view from db it has the columns "date,stockdelivered,sales" i want to add another calculated field know as "stock balance". i know this is supposed to be done at the client side during data entry. i have a script that generates php list/report only based on views and tables,it has no option for adding calculation fields, so i would like to make a view in mysql if possible.
我一直在 Excel 中做我的办公室工作。我的记录变得太多，想要使用 mysql。我从数据库中有一个视图它有列“日期，库存交付，销售”我想添加另一个计算字段，称为“库存余额”。我知道这应该在数据输入期间在客户端完成。我有一个脚本，它只根据视图和表格生成 php 列表/报告，它没有添加计算字段的选项，所以如果可能的话，我想在 mysql 中创建一个视图。
in excel i used to do it as follows.
在excel中，我曾经这样做过。
i would like to know if this is possible in mysql.
我想知道这在mysql中是否可行。
i don't have much experience with my sql but i imagine first one must be able to select the previous row.colomn4 then add it to the current row.colomn2 minus current row.colomn3
我对我的 sql 没有太多经验，但我想第一个必须能够选择上一个 row.colomn4 然后将它添加到当前 row.colomn2 减去当前 row.colomn3
If there is another way to achieve the same out put please suggest.
如果有另一种方法可以实现相同的输出，请提出建议。
采纳答案by Gordon Linoff
Eggyal has four good solutions. I think the cleanest way to do a running total in MySQL is using a correlated subquery -- it eliminates the group byat the end. So I would add to the list of options:
Eggyal 有四个很好的解决方案。我认为在 MySQL 中进行运行总计的最干净方法是使用相关子查询——它group by在最后消除了。所以我会添加到选项列表中：
SELECT sr.Sale_Date, sr.Stock_Delivered, sr.Units_Sold,
(select SUM(sr2.Stock_Delivered) - sum(sr2.Units_Sold)
from sales_report sr2
where sr2.sale_date <= sr.sale_date
) as StockBalance
FROM sales_report sr
ORDER BY Sale_Date
回答by eggyal
Generally speaking, SQL wasn't really intended to yield "running totals" like you desire. Other RDBMS have introduced proprietary extensions to deliver analytic functions which enable calculations of this sort, but MySQL lacks such features.
一般来说，SQL 并不是真的想产生您想要的“运行总数”。其他 RDBMS 引入了专有扩展来提供支持此类计算的分析函数，但 MySQL 缺乏此类功能。
Instead, one broadly has four options. In no particular order:
相反，一个大致有四种选择。没有特定的顺序：
Accumulate a running total in your application, as you loop over the resultset;
Alter your schema to keep track of a running total within your database (especially good in situations like this, where new data is only ever appended "to the end");
Group a self-join:
SELECT a.Sale_Date, SUM(a.Stock_Delivered) AS Stock_Delivered, SUM(a.Units_Sold) AS Units_Sold, SUM(b.Stock_Delivered - b.Units_Sold) AS `Stock Balance` FROM sales_report a JOIN sales_report b ON b.Sale_Date <= a.Sale_Date GROUP BY a.Sale_DateAccumulate the running total in a user variable:
SELECT Sale_Date, Stock_Delivered, Units_Sold, @t := @t + Stock_Delivered - Units_Sold AS `Stock Balance` FROM sales_report, (SELECT @t:=0) init ORDER BY Sale_Date
当您遍历结果集时，在您的应用程序中累积运行总数；
更改您的架构以跟踪数据库中的运行总数（在这种情况下尤其有用，新数据只会附加到“末尾”）；
分组自加入：
SELECT a.Sale_Date, SUM(a.Stock_Delivered) AS Stock_Delivered, SUM(a.Units_Sold) AS Units_Sold, SUM(b.Stock_Delivered - b.Units_Sold) AS `Stock Balance` FROM sales_report a JOIN sales_report b ON b.Sale_Date <= a.Sale_Date GROUP BY a.Sale_Date在用户变量中累积运行总数：
SELECT Sale_Date, Stock_Delivered, Units_Sold, @t := @t + Stock_Delivered - Units_Sold AS `Stock Balance` FROM sales_report, (SELECT @t:=0) init ORDER BY Sale_Date
回答by Brad
SELECT
sales_report.Stock_Delivered,
sales_report.Units_Sold,
sales_report.Stock_Delivered - sales_report.Units_Sold
FROM
sales_report;
