mysql - 多年mysql的动态列创建

Question

我为 MYSQL 编写了以下查询:

SELECT DATE_FORMAT(start, '%m/%e') as start,  (@total := @total + T.id) AS TotalApps, DATE_FORMAT(start, '%Y') as year FROM (SELECT start,COUNT(*) AS id FROM application WHERE start LIKE '2016%' GROUP BY MONTH(start), DAY(start)) AS T, (SELECT @total:=0) AS n

它按预期输出表格，其中包含开始日期、累计申请数和年份的列:

我想做的是删除 LIKE '2016%' 这样我就可以获得所有年份的数据并最终得到一个如下所示的表格:

等等。

Answer 1

该问题的累积性质使其与大多数其他关键问题大不相同。这是一种每年使用单独变量的方法:

SELECT DATE_FORMAT(start, '%m/%d') as mmdd, 
       (@t2016 := @t2016 + sum(year(start) = 2016)) as tot_2016,
       (@t2015 := @t2015 + sum(year(start) = 2015)) as tot_2015,
       (@t2014 := @t2014 + sum(year(start) = 2014)) as tot_2014
FROM application a CROSS JOIN
     (SELECT @t2016 := 0, @t2015 := 0, @t2014 := 0) params
GROUP BY DATE_FORMAT(start, '%m/%d')
ORDER BY mmdd;

编辑:

有时，group by 和变量不能一起工作。这是使用子查询修复的:

SELECT mmdd,
       (@t2016 := @t2016 + cnt_2016) as tot_2016,
       (@t2015 := @t2015 + cnt_2015) as tot_2015,
       (@t2014 := @t2014 + cnt_2014) as tot_2014
FROM (SELECT DATE_FORMAT(start, '%m/%d') as mmdd, 
             sum(year(start) = 2016)) as cnt_2016,
             sum(year(start) = 2015)) as cnt_2015,
             sum(year(start) = 2014)) as cnt_2014
      FROM application a 
      GROUP BY DATE_FORMAT(start, '%m/%d')
      ORDER BY mmdd
     ) x CROSS JOIN
     (SELECT @t2016 := 0, @t2015 := 0, @t2014 := 0) params;