mysql - 如何在只有一列是查询基础的 MySQL 中进行数据透视

Question

我看到了很多关于如何在 MySQL 中进行数据透视的方法，但我无法将其与我的情况联系起来。

我的情况是，我在 tbl_members 中有一个名为 date_of_mem 的列。我需要计算在那个特定的月份和年份注册了多少成员(member)。

例如:

我有一个名为 tbl_members 的表，其中的列名为 id、fname、mname、lname 和 date_of_mem 等等。

现在，我想要这样的结果:

Year | January | February | March | April | May | June | July ...
2013 | 100     | 250      | 50    | 60    | 300 | 280  | 125  ...
2014 | 101     | 240      | 20    | 40    | 200 | 180  | 145  ...

如何在 MySQL 中执行此操作。

提前致谢...

Answer 1

select      year(date_of_mem) as yr,
            sum(case when month(date_of_mem) = 1 then 1 else 0 end) as january,
            sum(case when month(date_of_mem) = 2 then 1 else 0 end) as february,
            sum(case when month(date_of_mem) = 3 then 1 else 0 end) as march,
            sum(case when month(date_of_mem) = 4 then 1 else 0 end) as april,
            sum(case when month(date_of_mem) = 5 then 1 else 0 end) as may,
            sum(case when month(date_of_mem) = 6 then 1 else 0 end) as june,
            sum(case when month(date_of_mem) = 7 then 1 else 0 end) as july,
            sum(case when month(date_of_mem) = 8 then 1 else 0 end) as august,
            sum(case when month(date_of_mem) = 9 then 1 else 0 end) as september,
            sum(case when month(date_of_mem) = 10 then 1 else 0 end) as october,
            sum(case when month(date_of_mem) = 11 then 1 else 0 end) as november,
            sum(case when month(date_of_mem) = 12 then 1 else 0 end) as december
from        tbl_members
group by    year(date_of_mem)