​​mysql计算金额的语句,选择语句中的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.

in excel i used to do it as follows.

i would like to know if this is possible in 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

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

解决方案

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

​以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；