带有日期的mysql单元格值作为带有年份的新列

Question

我有一个 mysql 数据库，如下所示:

|id  | city  |  created  |
|1   | city1 |2011-12-10 |
|2   | city2 |2010-02-01 |
|3   | city3 |2009-12-03 |
|4   | city1 |2012-11-16 |
|5   | city2 |2012-08-03 |
|6   | city3 |2011-10-01 |
|7   | city1 |2009-10-02 |
|8   | city2 |2011-12-01 |
|9   | city3 |2012-06-30 |

但我想要一个看起来像这样的替代表或 View

|city  | 2009 | 2010 | 2011 | 2012 |
|city1 |   1  |  0   | 1    | 1    |
|city2 |   0  |  1   | 1    | 1    |
|city3 |   1  |  1   | 0    | 1    |

Answer 1

也许是这样的:

SELECT
    YourTable.city,
    SUM(IF(YEAR(created)=2009,1,0)) AS '2009',
    SUM(IF(YEAR(created)=2010,1,0)) AS '2010',
    SUM(IF(YEAR(created)=2011,1,0)) AS '2011',
    SUM(IF(YEAR(created)=2012,1,0)) AS '2012'
FROM
    YourTable
GROUP BY
    YourTable.city