选择语句中的MySQL计算 [英] Mysql calculation in select statement

问题描述

我一直在Excel中做我的办公室工作，我的记录已经太多了，想使用mysql.i从db查看它具有日期，交货，销售"列，我想添加另一个计算字段被称为库存平衡". 我知道应该在数据输入期间在客户端完成此操作. 我有一个仅根据视图和表生成php列表/报告的脚本，它没有添加计算字段的选项，因此，如果可能的话，我想在mysql中进行视图.

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中，我通常按照以下步骤进行操作.

in excel i used to do it as follows.

我想知道在mysql中是否可行.

i would like to know if this is possible in mysql.

我对我的SQL没有太多经验，但我首先想到 一个人必须能够选择上一行. 然后将其添加到当前row.colomn2减去当前row.colomn3

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

如果还有另一种方法可以达到相同的效果，请提出建议.

If there is another way to achieve the same out put please suggest.

推荐答案

Eggyal有四个好的解决方案.我认为在MySQL中进行总运行的最干净的方法是使用相关的子查询-最终消除了group by.因此，我将添加到选项列表中:

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 by at the end. So I would add to the list of options:

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

这篇关于选择语句中的MySQL计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！