​​mysql计算金额的语句,选择语句中的MySQL计算!

​​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.

006fRELkly4gyhx6ytvlaj307z063wf5

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

006fRELkly4gyhx6yjprdj30as0lxwfe

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

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

电商数据分析360°实战攻略!

你将获得:

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群,共享资源。

5、优惠的会员商品。

6、一次付费只需99元,即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称:Excelbook
2、本站永久网址:http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场,并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报。
6、本站资源大多存储在云盘,如发现链接失效,请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>