mysql - 本周与去年同周财务数据对比

Question

我需要比较本周和去年同一周的财务数据..我的表格是

        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) 

Answer 1

您听起来没有信心了解自己想要的方式(或者更具体地说，您的老板希望如何将一年中的周值与另一年的周值关联起来(主要按月和和它可能会在一周或两周内发布)。

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

去年报告示例

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;