MYSQL 计算 YTD [英] MYSQL to calculate YTD

问题描述

我开始学习 MYSQL，但有一个我无法弄清楚的问题.我的桌子看起来像这样:

I am starting to learn MYSQL and have an issue at hand that I cannot figure out. My table looks like this:

OU 日期金额A20 201701 1000A20 201705 1000A20 201708 1000B10 201702 1000B10 201705 1000我想要的是按 OU 计算每一行的 YTD 金额，如下所示:

OU Date Amt A20 201701 1000 A20 201705 1000 A20 201708 1000 B10 201702 1000 B10 201705 1000 What I would like is to calculate the YTD amount for each row by OU that looks like this:

OU 日期 Amt YTDA20 201701 1000 1000A20 201705 1000 2000A20 201708 1000 3000B10 201702 1000 1000B10 201705 1000 2000

我尝试了不同的 sum 和 sumif 函数，但无法获得正确的结果.任何人都可以帮忙吗?非常感谢任何输入！

I tried different sum and sumif functions but couldn't get the correct results. Can anyone help? Any input is much appreciated!

推荐答案

一种方法使用相关子查询:

One approach uses a correlated subquery:

SELECT
    OU,
    Date,
    Amt,
    (SELECT SUM(t2.Amt) FROM yourTable t2
     WHERE t1.OU = t2.OU AND t2.Date <= t1.Date) AS YTD
FROM
    yourTable t1
ORDER BY
    OU, Date;

子查询使用的技巧是，从与当前记录共享相同 OU 值的那些记录中，为表中的每条记录求和当前或更早日期的数量.您可以浏览下面的演示以获得更好的理解.

The trick the subquery uses is to sum, for each record in your table, the amounts of the current or earlier dates, from those records sharing the same OU value as the current record. You can explore the demo below to get a better understanding.

此处演示:

Rextester

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