mysql - 比较本周和去年同一周的财务数据

我需要比较本周与去年同一周的财务数据。

        amount  storecode   date
        0.00    0000000001  2010-12-21 14:01:00
        0.00    0000000001  2010-12-21 14:01:00
        0.00    0000000001  2010-12-21 14:57:50
        10.00   0000000001  2010-12-21 14:57:50
        2.35    0000000001  2010-12-21 14:57:50
        45.00   0000000001  2010-12-21 14:57:50
        0.00    0000000001  2010-12-21 14:57:50
        -10.00  0000000001  2010-12-21 14:57:50
        -2.35   0000000001  2010-12-21 14:57:50
        -45.00  0000000001  2010-12-21 14:57:50
        ...................etc...etc to 2014

我尝试了许多不同的时间间隔，但是我找不到适合我的老板在本周和去年同一周之间进行比较的准确内容。

我什至尝试将其与另一个表连接起来，以便提供商店代码无脂肪视觉表示，而不是数字形式

        SELECT distinct(`stores`.`StoreLocation`) as branch, SUM(`TRANSACTIONS`.`Amount`) as AmountTendered , DATE(`TRANSACTIONS`.`Date`) as Dates FROM transactions LEFT JOIN stores ON stores.StoreCode = transactions.Storecode WHERE DATE_SUB(CURDATE(),INTERVAL 1 year) <= DATE(Date) group by YEAR(Date)

最佳答案

您听起来不确定自己了解自己想要的方式（或更具体地说，您的老板希望如何将一年的周值与另一年的值相关联（主要是按月进行，可能要花一周或两周的时间）。

这是基于您共享的数据的起点

去年报告的例子

SELECT YEAR(`date`) AS `year`
    , WEEKOFYEAR(`date`) AS weekno
    ,Storecode AS storecode
    , SUM(amount) AS amount
FROM transactions
WHERE YEAR(`date`) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))
GROUP BY YEAR(`date`), WEEKOFYEAR(`date`), Storecode

这是带有比较的查询示例

SELECT this.storecode
   , this.weekno
   , this.amount AS current_amount
   , history.amount AS past_amount
FROM (SELECT YEAR(`date`) AS `year`
        , WEEKOFYEAR(`date`) AS weekno
        ,Storecode AS storecode
        , SUM(amount) AS amount
      FROM transactions
      WHERE YEAR(`date`) = YEAR(NOW())
      GROUP BY YEAR(`date`), WEEKOFYEAR(`date`), Storecode) AS this
JOIN (SELECT YEAR(`date`) AS `year`
        , WEEKOFYEAR(`date`) AS weekno
        ,Storecode AS storecode
        , SUM(amount) AS amount
      FROM transactions
      WHERE YEAR(`date`) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))
      GROUP BY YEAR(`date`), WEEKOFYEAR(`date`), Storecode) AS history
  ON this.weekno = history.weekno
    AND this.storecode = history.storecode;

关于mysql - 比较本周和去年同一周的财务数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22986618/