mysql - 计算多年的数据并按位置分组

Question

我想制作一个关于某个地区人口的图表，这是我的表格的示例；

mmr                              mmrp
+-------+--------+--------+     +-------+------------+
| rm    |city    |region  |     | rm    | date       |
+-------+--------+--------+     +-------+------------+
| 123   |city_a  |region1 |     | 123   | 2012-xx-xx |
| 124   |city_a  |region1 |     | 124   | 2013-xx-xx |
| 210   |city_b  |region1 |     | 210   | 2012-xx-xx |
| 211   |city_c  |region2 |     | 211   | 2011-xx-xx |
| 212   |city_d  |region3 |     | 212   | 2011-xx-xx |
| 213   |city_e  |region3 |     | 213   | 2013-xx-xx |
+-------+--------+--------+     +-------+------------+

我想统计 在 region1 按城市分组 的人数，以及 在 region1 按地区分组 的人数，以及 3 年内的人数，这就是我要实现的目标；

+-------+--------+--------+--------+
| area  | 2011   | 2012   | 2013   |
+-------+--------+--------+--------+
|city_a | 0      | 1      | 1      |
|city_b | 0      | 1      | 0      |
|region2| 1      | 0      | 0      |
|region3| 1      | 0      | 1      |
+-------+--------+--------+--------+

到目前为止，我只知道如何在特定的地区和年份进行计数，这就是我所做的；

SELECT a.city, count(b.rm) as 2013
FROM mmr a join mrpp b on (a.rm=b.rm)
where year(b.date) = 2013 and a.region like 'region1' GROUP BY city

但它只显示2013年region1的计数，有什么办法吗？

Answer 1

SELECT IF(a.region = 'region1', city, region) AS area,
       SUM(year(b.date) = 2011) AS `2011`,
       SUM(year(b.date) = 2012) AS `2012`,
       SUM(year(b.date) = 2013) AS `2013`
FROM mmr a JOIN mrpp b ON a.rm = b.rm
WHERE b.date >= `2011-01-01`
GROUP BY area