Mysql 查询以并排比较过去 3 年的每周数据

Question

我需要一些帮助来创建 mysql 语句。我有记录每日萨尔斯数据的销售表。现在，我想并排比较过去 3 年的每周销售额。

下面是 sqlfiddle 中的示例数据。

这是我到目前为止尝试过的，但只返回了一周的数据：

SELECT  YEARWEEK(s1.sale_date,1) AS weekY1, SUM(s1.total) AS totalY1, 
                    YEARWEEK(s2.sale_date,1) AS weekY2, SUM(s2.total) AS totalY2,
                    YEARWEEK(s3.sale_date,1) AS weekY3, SUM(s3.total) AS totalY3
            from sales_chc s1
            LEFT JOIN sales_chc s2
            ON s2.sale_date = (s1.sale_date -INTERVAL 1 YEAR)
            LEFT JOIN sales_chc s3
            ON s3.sale_date = (s1.sale_date -INTERVAL 2 YEAR)
            WHERE YEAR(s1.sale_date) = '2017' AND YEARWEEK(s1.sale_date, 1) = YEARWEEK(CURDATE(), 1)-1
            GROUP BY YEARWEEK(s1.sale_date, 1)

Answer 1

试试这个：

SELECT WEEKOFYEAR(s1.sale_date) as WEEKNUM, 
SUM(case when YEAR(s1.sale_date) = '2017' then  s1.total else 0 end) AS total2017,
SUM(case when YEAR(s1.sale_date) = '2016' then s1.total else 0 end) AS total2016, 
SUM(case when YEAR(s1.sale_date) = '2015' then s1.total else 0 end) AS total2015 
from sales_chc s1
GROUP BY WEEKOFYEAR(s1.sale_date)