mysql - 将记录值显示为列

Question

我有一个这样的表:

    X    |      Y      |  Z
---------+-------------+-----
3136     |   2014-03   |  A
700      |   2014-03   |  B
531      |   2014-03   |  C
1575     |   2014-03   |  D
2776     |   2014-04   |  A
1200     |   2014-04   |  B
148      |   2014-04   |  C
903      |   2014-04   |  D

我想把这张表转换成类似的东西:

Date        |  A   |  B   |  C   |  D
------------+------+------+------+------
2014-03     | 3136 | 700  | 531  | 1575
2014-04     | 2776 | 1200 | 148  | 903

如何通过一次选择完成？

Answer 1

SELECT y as Date,
SUM(CASE WHEN Z='A' THEN X ELSE 0 END) as A,
SUM(CASE WHEN Z='B' THEN X ELSE 0 END) as B,
SUM(CASE WHEN Z='C' THEN X ELSE 0 END) as C,
SUM(CASE WHEN Z='D' THEN X ELSE 0 END) as D
FROM table
GROUP BY y