MySQL 按年列和月份总计

Question

寻找有关如何最好地提取一些数据的建议。我需要做一些计算，但按年和月提取数据来制作这样的表格。如果有办法让年份由范围自动生成，那就更好了。

        2012    2013    2014Jan     $243    $567    $890Feb     $123    $456    $908Mar     ...       ...AprMay

I can get the data to be ordered by year, month but as you know that is not easily conducive to putting it into an HTML table without some middle ware storing and iterating through found sets.

I've used the following query which is close but not exact.

select year(date_donated) as year, monthname(date_donated) as month, sum(amount_donated) as total_donations, count(date_donated) as count_donations from my_donations where (date_donated between "2012-01-01" and "2014-12-31") group by year(date_donated), month(date_donated) order by field(month, 'January','February','March','April','May','June','July','August','September','October','November','December')

Answer 1

首先，在客户端执行此操作要容易得多。

但是，如果由于某种原因您仍然希望在数据库端转移数据，您可以利用动态 SQL。

如果您想要连续数年和数月，而不管数据如何，您还需要某种计数(数字)表。例如。喜欢这个

CREATE VIEW tally AS
  SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2 UNION ALL 
  SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5 UNION ALL 
  SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8 UNION ALL 
  SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11;

现在是动态 SQL 部分:

SET @sql = NULL;

SELECT GROUP_CONCAT(CONCAT('SUM(CASE WHEN YEAR(date_donated) = ', year_start + n, ' THEN amount_donated END) `', year_start + n, '`'))
  INTO @sql
  FROM tally CROSS JOIN (SELECT 2012 year_start, 2014 year_end) y
 WHERE n <= year_end - year_start 
 ORDER BY n;

SET @sql = CONCAT('SELECT MONTHNAME(''2000-01-01'' + INTERVAL n MONTH) month,', @sql,
' FROM tally t LEFT JOIN 
(
   SELECT date_donated, amount_donated
     FROM my_donations
    WHERE date_donated BETWEEN ''', 2012, '-01-01'' AND ''', 2014, '-12-31'' 
) d
  ON t.n + 1 = MONTH(date_donated)                
GROUP BY t.n 
ORDER BY t.n');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

输出:

|     MONTH |   2012 |   2013 |   2014 ||-----------|--------|--------|--------||   January |    243 |    567 |    890 ||  February |    123 |    456 |    908 ||     March | (null) | (null) | (null) |...

这是一个 SQLFiddle 演示