MySql没有 PIVOT ，因此您必须使用条件 SUM().

MySql doesn't have PIVOT so you have to use conditional SUM().

有两种选择:

1. 如果周期数是有限的并且事先已知，则您可以手动执行
2. 您可以使用动态SQL即时生成查询，然后执行该查询

对option1的查询可能如下所示

A query for option1 might look like this

SELECT productid
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201210' THEN 1 ELSE 0 END) `Oct12`
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201211' THEN 1 ELSE 0 END) `Nov12`      
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201212' THEN 1 ELSE 0 END) `Dec12`
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201301' THEN 1 ELSE 0 END) `Jan13`
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201302' THEN 1 ELSE 0 END) `Feb13`      
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201303' THEN 1 ELSE 0 END) `Mar13`
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201304' THEN 1 ELSE 0 END) `Apr13`
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201305' THEN 1 ELSE 0 END) `May13`      
      ,SUM(CASE WHEN DATE_FORMAT(sales_date, '%Y%m') = '201306' THEN 1 ELSE 0 END) `Jun13`
 FROM sales
WHERE sales_date BETWEEN '2012-10-01' AND '2013-06-30'
GROUP BY productid

示例输出:

| PRODUCTID | OCT12 | NOV12 | DEC12 | JAN13 | FEB13 | MAR13 | APR13 | MAY13 | JUN13 |
-------------------------------------------------------------------------------------
|         1 |     0 |     0 |     0 |     1 |     1 |     0 |     0 |     2 |     1 |
|         2 |     1 |     1 |     1 |     0 |     0 |     1 |     1 |     1 |     2 |

这里是 SQLFiddle 演示

---

现在是使用动态SQL的选项2的版本( PREPARE ， EXECUTE )

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN DATE_FORMAT(sales_date, ''%Y%m'') = ''',
      DATE_FORMAT(period, '%Y%m'), ''' THEN 1 ELSE 0 END) `', DATE_FORMAT(period, '%b%y'), '`'
    )
  ) INTO @sql
FROM
(
    SELECT DATE_FORMAT(sales_date, '%Y-%m-01') period
      FROM sales
     WHERE sales_date BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, '%Y-%m-01') AND CURDATE()
     GROUP BY DATE_FORMAT(sales_date, '%Y-%m-01')
) s;

SET @sql = CONCAT
('SELECT productid, ', @sql, ' 
    FROM sales
   WHERE sales_date BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, ''%Y-%m-01'') AND CURDATE()
   GROUP BY productid'
);

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

输出是相同的.

| PRODUCTID | JUL12 | AUG12 | SEP12 | OCT12 | NOV12 | DEC12 | JAN13 | FEB13 | MAR13 | APR13 | MAY13 | JUN13 |
-------------------------------------------------------------------------------------------------------------
|         1 |     1 |     2 |     1 |     0 |     0 |     0 |     1 |     1 |     0 |     0 |     2 |     1 |
|         2 |     0 |     0 |     0 |     1 |     1 |     1 |     0 |     0 |     1 |     1 |     1 |     2 |

这里是 SQLFiddle 演示

Here is SQLFiddle demo